Change the variable names

[A_Swoosh]

Hi SAS community,

I have two datasets where I'm trying to change the variable names in one dataset to match another. The variables that are particularly important are quarterly variables which don't correspond in both datasets.

For example,

Dataset 1:

q12018_units q12018_stdunits q22018_units q22018_stdunits and so on...

Dataset2:

q1_2018_units q2_2018_units ... q1_2018_std_units q1_2018_std_units and so on...

I'm trying to change the variable names from dataset 1 to match those from dataset 2.

Code I had:

proc transpose data=a2(obs=0) out=temp1 (DROP=_label_);
       var _all_;
run;

PROC SORT DATA=TEMP1; 
      BY _ALL_;
RUN;

proc transpose data=b1(obs=0) out=temp2 (DROP=_label_);
       var _all_;
run;

data temp;
      merge TEMP1(rename=(_name_=name)) TEMP2;
     name=lowcase(name);
     _name_=upcase(_name_);
run;

data _null_;
      set temp end=last;
       if _n_=1 then 
       call execute('proc datasets library=work nolist nodetails;modify b1;rename ');
       call execute(catx('=',_name_,name));
       if last then call execute(';quit;');
run;

The two datasets aren't in the same order so it's making life a little more difficult to match these. When I apply this code, they don't match to the corresponding value despite being similar in the temp dataset. Any advice would be appreciated as I'm stumped. 

[ballardw]

Here's a radical idea. Instead of fixing a problem go back in your process and make sure the names are the same for which ever is the problem set.

It is usually easiest to make sure that your read step gets the right names than to fix them later.

[japelin]

I tried to create a dataset like this, but it was renamed by your code without any problem.

data have1;
  length q12018_units q12018_stdunits q22018_units q22018_stdunits 8;
run;

Data have2;
  length q1_2018_units q2_2018_units q1_2018_std_units q2_2018_std_units 8;
run;

Can you please present the two datasets in question?

Then you can compare the list of variables in proc contents.
You may find that there are some variable names that do not match, and that may be affecting the results.

proc contents data=a2 out=cont_a2(keep=MEMNAME NAME TYPE);
run;
proc contents data=b1 out=cont_b1(keep=MEMNAME NAME TYPE);
run;

[mkeintz · Accepted solution]

First, I agree with @ballardw - fix the original dataset with the unwanted names.

BUT if your were to do this with code, and all the rename-able variables have the structure Qnyyyy_UNITS and Qnyyyy_STDUNITS, then you should use the dictionary feature of proc sql instead of transposing variable names,  Something like:

proc sql noprint;
  select distinct  catx('=',name,tranwrd(cats(substr(name,1,2),'_',substr(name,3)),'stdunits','std_units')) 
  into :renames separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='DATASET1';
quit;

%put &=renames;

data want;
  set dataset1 (rename=(&renames))  dataset2;
run;

The SELECT ... INTO expression creates a macrovar, named RENAMES in this case.  The 

   %put &=renames;

statement will display it on the sas log.  It will look something like

    q12018_units=q1_2018_units   q12018_stdunits=q1_2018_std_units

Then you can combine the datasets, renaming variables in dataset1.

[andreas_lds]

It is hardly possible to use the names in the second dataset in the renaming process, because matching them with the names in first dataset is not possible. Instead you could use functions to change the names so that they match those in the second dataset:

data have1;
  length q12018_units q12018_stdunits q22018_units q22018_stdunits 8;
  call missing(of _all_);
run;

proc print data=have1;run;

data work.debug(keep= Name newName);
   set sashelp.vcolumn end= jobDone;
   where Libname = 'WORK' and MemName = 'HAVE1';
   
   length newName $ 32;
   
   if _n_ = 1 then do;
      call execute('proc datasets library= work nolist; modify Have1; rename');
   end;
   
   newName = cats(substr(Name, 1, 2), '_', substr(Name, 3));
   newName = tranwrd(newName, 'stdunits', 'std_units');
   
   call execute(cats(Name, '=', NewName));
   
   if jobDone then do;
      call execute(';quit;');
   end;
run;

proc print data=have1;run;