SAS: Rename variables in merge according to original dataset

Question

I have two datasets, one for male and one for female, which contain identical variables. I need to find the percent difference between the sexes on each variable by group.

The datasets look something like this, but with more variables and groups,

| Group | Sex | VarA | VarB |
|-------+-----+------+------|
|     1 | F   |    8 |    5 |
|     2 | F   |    6 |    3 |
|     3 | F   |    7 |    0 |
|-------+-----+------+------|

| Group | Sex | VarA | VarB |
|-------+-----+------+------|
|     1 | M   |    9 |    7 |
|     2 | M   |    8 |    5 |
|     3 | M   |    6 |    3 |
|-------+-----+------+------|

The result I need is this:

| Group | percent_diffA | percent_diffB |
|-------+---------------+---------------|
|     1 |  -0.117647059 |  -0.333333333 |
|     2 |  -0.285714286 |          -0.5 |
|     3 |   0.153846154 |            -2 |
|-------+---------------+---------------|

I could solve this via a merge by renaming each variable.

data difference;
  merge
    females (rename = (VarA = VarA_F VarB = VarB_F)
    males   (rename = (VarA = VarA_M VarB = VarB_M)
    ;
  by group;

  percent_diffA = (VarA_F - VarA_M) / ( (VarA_F + VarA_M) / 2 );
  percent_diffB = (VarB_F - VarB_M) / ( (VarB_F + VarB_M) / 2 );

  drop sex;
run;

However, this approach requires me to rename everything manually. With several variables, the rename statement becomes cumbersome. Unfortunately, this calculation is being interjected into some old code, so renaming the original datasets is not practical.

I'm wondering if there is another way to solve this problem which is less cumbersome.

EDIT: I have updated the variable names because that appears to have caused people confusion. They were originally called Var1 and Var2. They are now VarA and VarB. The real variable names are descriptive, for instance body_weight_g or gonadal_somatic_index. The variables are not simply listed with sequential numbers.

Answer 1

Shenglin's answer is a nice and concise use of SQL. An alternative method is constructing a macro variable specifying the renames to be used in the rename DSO (data set option). This can be done with an SQL query to the dictionary table containing the column names.

* This macro creates the macro variable rename_suffix, to be used in a rename statement or data set option ;
* It will be of form: var1 = var1_suffix var2 = var2_suffix ... ;
* &inset is the input set. &suffix is the suffix to added to all variables except for the variables specified in &keys. ; 
* &keys variables should be given each in quotation marks, and separated by spaces. ;
%macro rename_list(inset, suffix, keys) ;
    %global rename_&inset ; * So that this macro variable is accessable outside the macro ;
    proc sql ;
        select strip(name) || ' = ' || strip(name) || "_&suffix"
            into :rename_&inset separated by ' '
            from sashelp.vcolumn /* dictionary.columns can be used in place of sashelp.vcolumn */
                where libname = 'WORK' & memname = "%sysfunc(upcase(&inset))" 
                      & upcase(strip(name)) not in (' ' %sysfunc(upcase(&keys)));   * The ' ' is included, so there is no error if no keys are given ;
    quit ;
%mend rename_list ; 

%rename_list(females, F, 'GROUP' 'SEX')
%rename_list(males  , M, 'GROUP' 'SEX')
%put &rename_females ; * Check that the macro variables are correct ;
%put &rename_males ;

%macro pct_diff(num) ;
    percent_diff&num = (Var&num._F - Var&num._M) / ( (Var&num._F + Var&num._M) / 2 ) ;
%mend pct_diff ;

data difference ;
    merge females(rename = (&rename_females), drop = sex) 
          males  (rename = (&rename_males  ), drop = sex) ;
    by group ;

    pct_diff(1) ;
    pct_diff(2) ;
run ;
dm 'vt difference';

The percent_diff variable creation can also be shortened with a macro (as shown). If you had a large and/or variable number of variables to compare, then you could further shorten it by automatically detecting the number of comparisons, by running the same SQL query with the select into part modified to be

select count(name) into :varct trimmed

to count the number of variables, and then use a do loop in the data step:

    do i = 1 to &varct ;
        %pct_diff(i) ;
    end ;

Answer 2

For a data set that contains variables that are sequentially numbered there is variable list syntax for renaming the whole range of variables:

This example creates sample that has 100 variables.

data have1 have2;
  do group = 1 to 100;
    sex = 'M';
    array var(100);
    do _n_ = 1 to dim(var);
      var(_n_) = ceil (25 * ranuni(123));
    end;
    if group ne 42 then output have1;
    sex = 'F';
    do _n_ = 1 to dim(var);
      var(_n_) = ceil (25 * ranuni(123));
    end;
    if group ne 100-42 then output have2;
  end;
run;

The rename option works on all 100 variables.

data want;
  merge 
    have1(rename=var1-var100=mvar1-mvar100 in=_M)
    have2(rename=var1-var100=fvar1-fvar100 in=_F)
  ;

  by group;

  if _M & _F & first.group & last.group then do;

    array one mvar1-mvar100;
    array two fvar1-fvar100;
    array results result1-result100; 

    do i = 1 to dim(results);
      diff = one(i) - two(i);
      mean = mean (one(i), two(i));
      results(i) = diff / mean * 100;
    end;

  end;

  keep group result:;
run;

Answer 3

Use table alias in proc sql to avoid name change:

proc sql;
   select a.group,(a.var1-b.var1)/((a.var1+b.var1)/2) as percent_diff1, 
   (a.var2-b.var2)/((a.var2+b.var2)/2) as percent_diff2
   from female as a,male as b
   where a.group=b.group;
quit;