BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

/* 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;
PG

View solution in original post

4 REPLIES 4
Ksharp
Super User

It should be 

 

proc datasets library=work noprint nolist;
  modify sample;
 rename a1=Col_1;
 rename a2=Col_2;
quit;
PGStats
Opal | Level 21

/* 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;
PG
ayin
Quartz | Level 8

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;'); 

 

ballardw
Super User

Proc datasets wants a LIBRARY not a dataset on the proc statement and a MODIFY with the dataset name before the rename/label etc.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3756 views
  • 4 likes
  • 4 in conversation