I want to produce a macro that converts the variable names of a dataset into the variables' labels. I intend to apply this macro for large datasets where manually changing the variable names would be impractical.
I came across this code online from the SAS website, which looked promising but produced errors. I made slight edits to remove some errors. It now works for their sample dataset but not mine. Any assistance with improving this code to work with my sample dataset would be greatly appreciated!
SAS sample dataset (works with code):
data t1;
label x='this_x' y='that_y';
do x=1,2;
do y=3,4;
z=100;
output;
end;
end;
run;
My sample dataset (does not work with code):
data t1;
input number group;
label number = number_lab group = group_lab;
datalines;
1 1
1 .
2 1
2 .
3 2
3 .
4 1
4 .
5 2
5 .
6 1
6 .
;
run;
Code:
%macro chge(dsn);
%let dsid=%sysfunc(open(&dsn));
%let cnt=%sysfunc(attrn(&dsid,nvars));
%do i= 1 %to &cnt;
%let var&i=%sysfunc(varname(&dsid,&i));
%let lab&i=%sysfunc(varlabel(&dsid,&i));
%if lab&i = %then %let lab&i=&&var&i;
%end;
%let rc=%sysfunc(close(&dsid));
proc datasets;
modify &dsn;
rename
%do j = 1 %to &cnt;
%if &&var&j ne &&lab&j %then %do;
&&var&j=&&lab&j
%end;
%end;
quit;
run;
%mend chge;
%chge(t1)
proc contents;
run;
My code produces the following error messages:
ERROR 73-322: Expecting an =.
ERROR 76-322: Syntax error, statement will be ignored.
Mainly you are not closing the RENAME statement with a semi-colon. But it also looks like you have the RUN and QUIT statements in the wrong order.
But note that there is no need for that complex %sysfunc() macro code to get the list of names and labels. Since you are already generating a PROC DATASETS step your macro can generate other SAS code also. Then your macro will be clearer and easier to debug.
%macro chge(dsn);
%local rename ;
proc contents data=&dsn noprint out=__cont; run;
proc sql noprint ;
select catx('=',nliteral(name),nliteral(label))
into :rename separated by ' '
from __cont
where name ne label and not missing(label)
;
quit;
%if (&sqlobs) %then %do;
proc datasets nolist;
modify &dsn;
rename &rename ;
run;
quit;
%end;
%mend chge;
If the list of rename pairs is too long to fit into a single macro variable then you could resort to generating two series of macro variables using PROC SQL and then add back your %DO loop.
Here is SAS log from testing on your sample file.
4156 %chge(t1);
MPRINT(CHGE): proc contents data=t1 noprint out=__cont;
MPRINT(CHGE): run;
NOTE: The data set WORK.__CONT has 2 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.08 seconds
cpu time 0.01 seconds
MPRINT(CHGE): proc sql noprint ;
MPRINT(CHGE): select catx('=',nliteral(name),nliteral(label)) into :rename
separated by ' ' from __cont where name ne label and not missing(label) ;
MPRINT(CHGE): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds
MPRINT(CHGE): proc datasets nolist;
MPRINT(CHGE): modify t1;
MPRINT(CHGE): rename group=group_lab number=number_lab ;
NOTE: Renaming variable group to group_lab.
NOTE: Renaming variable number to number_lab.
MPRINT(CHGE): run;
NOTE: MODIFY was successful for WORK.T1.DATA.
MPRINT(CHGE): quit;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.12 seconds
cpu time 0.00 seconds
Note that if I now try to run it again on the modified dataset it does not rename anything.
4157 %chge(t1);
MPRINT(CHGE): proc contents data=t1 noprint out=__cont;
MPRINT(CHGE): run;
NOTE: The data set WORK.__CONT has 2 observations and 41 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
MPRINT(CHGE): proc sql noprint ;
MPRINT(CHGE): select catx('=',nliteral(name),nliteral(label)) into :rename
separated by ' ' from __cont where name ne label and not missing(label) ;
NOTE: No rows were selected.
MPRINT(CHGE): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.08 seconds
cpu time 0.00 seconds