topic Re: Renaming variables through a macro in SAS Programming

Renaming variables through a macro

dbjosiah — Thu, 20 May 2021 00:43:18 GMT

Hi, my project reads in variables from several datasets, and I need a macro that adds a prefix to each variable name identifying which dataset it came from. In this example the prefix is "CI_". A complication is that some of the original variable names are at the SAS limit of 32 characters. I've found several helpful previous posts, in particular Ksharp's response at https://communities.sas.com/t5/SAS-Procedures/Proc-SQL-Code-working-in-SAS-9-4-but-not-SAS-9-3-nvar-to/td-p/285174:

%macro rename(lib,dsn,newname);
 data _null_;
  set sashelp.vcolumn(where=(libname="%upcase(&lib)" and memname="%upcase(&dsn)")) end=last;
  if _n_=1 then call execute("proc datasets library=&lib nodetails nolist; modify &dsn; rename ");
  call execute(catt(name,'=&newname._',name));
  if last then call execute(';quit;');
 run;
%mend rename;
%rename(WORK,B,Try1)

This is my version:

%macro rename(lib,dsn,prefix);
data _null_;
if _n_=1 then call execute("proc datasets library=&lib nodetails nolist; modify &dsn; rename ");
  set sashelp.vcolumn(where=(libname="%upcase(&lib)" and memname="%upcase(&dsn)")) end=last;
  vlength=length(name);
  if vlength>25 then do; /*Taking out underscores to shorten the vbl name*/
	put '1. Before: ' name= vlength=;
    name=compress(translate(trim(name),'','_'));
	put '1. After: ' name= vlength=;
  end;
   call execute(catt(name,'=&prefix._',name));
  if last then call execute(';quit;');
 run;
%mend rename;
%rename(WORK,case6,CI)

This successfully writes to the log:

1. Before: _N_=1 name=_Diagnosis_Date_time__Associated vlength=32
1. After: _N_=1 name=DiagnosisDatetimeAssociated vlength=27
...
NOTE: CALL EXECUTE generated line.
1 + proc datasets library=WORK nodetails nolist; modify case6;
1 + rename
2 + DiagnosisDatetimeAssociated=CI_DiagnosisDatetimeAssociated
3 + VAR3=CI_VAR3
...

but then gives an error message for the renamed variables:

ERROR: Variable DiagnosisDatetimeAssociated is not on file WORK.CASE6.
NOTE: Renaming variable VAR3 to CI_VAR3.

Hopefully it's something simple but I'm not good at manipulating Proc Datasets.

Thanks for any suggestions!

Re: Renaming variables through a macro

r_behata — Thu, 20 May 2021 01:39:59 GMT

Works fine with a small modification. The before and after variable name should be different.

%macro rename(lib,dsn,prefix);
data _null_;
if _n_=1 then call execute("proc datasets library=work nodetails nolist; modify &dsn; rename ");
  set sashelp.vcolumn(where=(libname="%upcase(&lib)" and memname="%upcase(&dsn)")) end=last;
  vlength=length(name);
  if vlength>25 then do; /*Taking out underscores to shorten the vbl name*/
	put '1. Before: ' name= vlength=;
    newname=compress(translate(trim(name),'','_'));
	put '1. After: ' newname= vlength=;
  end;
   call execute(catt(name,'=&prefix._',newname));
  if last then call execute(';quit;');
 run;
%mend rename;
%rename(WORK,case6,CI)

 1         + proc datasets library=work nodetails nolist; modify case6;
 1         +                                                            rename
 2         + _Diagnosis_Date_time__Associated=CI_DiagnosisDatetimeAssociated
 3         + ;
 NOTE: Renaming variable _Diagnosis_Date_time__Associated to CI_DiagnosisDatetimeAssociated.
 3         +  quit;

Re: Renaming variables through a macro

dbjosiah — Thu, 20 May 2021 15:05:44 GMT

Thank you! I had to add a line to rename the original variables whose names weren't too long, but then it worked perfectly. One further question - because it successfully renamed all the variables in the original dataset, the next time I ran the same code to work out other little bugs it read in the already renamed variables and was not happy. Is there a way to read in the original data and save the results with a new dataset name? Once all the bugs are clear this won't be a fatal issue, it just would be nice to have

Re: Renaming variables through a macro

r_behata — Thu, 20 May 2021 15:37:45 GMT

I would suggest that you to start a new thread for the issues you are facing as a consequence of renaming the original variables in your existing code. Please provide some samples.

Re: Renaming variables through a macro

dbjosiah — Thu, 20 May 2021 15:43:03 GMT

Since it's not critical, I'll do that when have a bit of free time. Thanks again for your solution!