topic Re: Rename columns without knowing original column name but its labels. in SAS Enterprise Guide

Rename columns without knowing original column name but its labels.

ayin — Fri, 21 Apr 2017 02:31:40 GMT

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?

Re: Rename columns without knowing original column name but its labels.

Ksharp — Fri, 21 Apr 2017 02:35:54 GMT

It should be

proc datasets library=work noprint nolist;
  modify sample;
 rename a1=Col_1;
 rename a2=Col_2;
quit;

Re: Rename columns without knowing original column name but its labels.

PGStats — Fri, 21 Apr 2017 02:36:43 GMT


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

Re: Rename columns without knowing original column name but its labels.

ayin — Fri, 21 Apr 2017 02:39:57 GMT

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

Re: Rename columns without knowing original column name but its labels.

ballardw — Fri, 21 Apr 2017 17:05:31 GMT

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