Have:
column reference table (named 'Col_Ref'), that looks like -
Standard Label
Col_1 Are you kidding
Col_2 How to do it
dataset (named 'sample'), that looks like -
a1 a2 /* the label for a1 is 'Are you kidding', and for a2 is 'How to do it' */
3 char
Want:
rename the columns in the dataset 'sample' to:
Col_1 Col_2
3 char
Note:
To generate the datasets:
data Col_Ref;
input Standard $ Label $char20.;
cards;
Col_1 Are you kidding
Col_2 How to do it
;
run;
data sample;
input a1 a2 $;
label a1 = 'Are you kidding'
a2 = 'How to do it';
cards;
3 char
;
run;
To get a "reference" table:
proc sql;
create table test as
select name, label from sashelp.vcolumn
where upcase(libname) = 'WORK' and upcase(memname) = 'SAMPLE';
create table test2 as
select a.*,b.Standard from test a
left join Col_Ref b
on a.label = b.Label;
quit;
To rename using the "reference table":
data _null_;
set test2 end=last;
if _n_=1 then call execute('proc datasets data=work.sample noprint nolist;');
call execute(cat('rename ',strip(name),'=',strip(standard),';'));
if last then call execute(';run;quit;');
run;
Questions:
It is reporting an error:
NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1 + proc datasets data=work.sample noprint nolist;
____
22
202
NOTE: Enter RUN; to continue or QUIT; to end the procedure.
ERROR 22-322: Syntax error, expecting one of the following: ;, ALTER, DD, DDNAME, DETAILS, FORCE, GENNUM, KILL, LIB, LIBRARY,
MEMTYPE, MT, MTYPE, NODETAILS, NOFS, NOLIST, NOPRINT, NOWARN, PROTECT, PW, READ.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
2 + rename a1=Col_1;
3 + rename a2=Col_2;
4 + ;run;
NOTE: Statements not processed because of errors noted above.
4 + quit;
How to fix it?
/* Find matching labels */
proc sql;
select catx("=", a.name, b.standard)
into :rename separated by " "
from
dictionary.columns as a inner join
col_ref as b on a.label=b.label
where a.libname="WORK" and upcase(a.memname)="SAMPLE";
quit;
/* Rename variables */
data want;
set sample;
rename &rename;
run;
It should be
proc datasets library=work noprint nolist;
modify sample;
rename a1=Col_1;
rename a2=Col_2;
quit;
/* Find matching labels */
proc sql;
select catx("=", a.name, b.standard)
into :rename separated by " "
from
dictionary.columns as a inner join
col_ref as b on a.label=b.label
where a.libname="WORK" and upcase(a.memname)="SAMPLE";
quit;
/* Rename variables */
data want;
set sample;
rename &rename;
run;
Fixed the problem by changing the code:
call execute('proc datasets data=work.sample noprint nolist;');
to:
call execute('data work.sample_final; set work.sample;');
Proc datasets wants a LIBRARY not a dataset on the proc statement and a MODIFY with the dataset name before the rename/label etc.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.