topic Re: Proc SQL to add a suffix to column variable names in SAS Programming

Proc SQL to add a suffix to column variable names

KaraG — Fri, 07 May 2021 19:41:32 GMT

Hi everyone,

I'm trying to add a suffix to all column names in a dataset. I'm following the code (example 2) here. I have never used proc SQL before. The first portion of the code (the sql part) seems to run ok (no error messages). When I run the second part (proc datasets), I get the following messages:

86 proc datasets library = work nolist;

87 modify meanCPUAbottomfish;

WARNING: Apparent symbolic reference LIST not resolved.

88 rename &list; run;

NOTE: Enter RUN; to continue or QUIT; to end the procedure.

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: Statements not processed because of errors noted above.

89 quit;

How do I fix this error? Thanks.

Re: Proc SQL to add a suffix to column variable names

Reeza — Fri, 07 May 2021 20:03:16 GMT

It's saying your macro variable list doesn't exist.
Please show how you created the macro variable, the log and the output from the following, before the PROC DATASETS proc:

%put &list.;

Re: Proc SQL to add a suffix to column variable names

KaraG — Fri, 07 May 2021 21:13:40 GMT

Hi, thank you so much for replying. I actually just tried the code again with the dataset name capitalized and it worked...?

Here's the code:
/* Example 1: */
/* This code creates a macro variable &list with the list of variables in the form. */
/* id = id_OLD */
/* This format could be used to add a suffix to all the variables. */
proc sql noprint;
select cats(name,'=',name,'_bottom')
into :list
separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'MEANCPUABOTTOMFISH';
quit;
*/
/* Use PROC DATASETS to do the rename using the macro variable you have created. */
/* Modify the libref and data set name for your data set. */;
proc datasets library = work nolist;
modify meanCPUAbottomfish;
rename &list; run;
quit;
/* Verify the changes with a PROC CONTENTS on your data set. */
proc contents data = MEANCPUABOTTOMFISH;
run;

Re: Proc SQL to add a suffix to column variable names

Reeza — Fri, 07 May 2021 21:45:12 GMT

That is correct, if you look in Dictionary.Columns table it always stores the libname and memname in upper case letters.
Happy to hear your issue is resolved.

A somewhat safer method in the long run is to up case your comparison all the time, but that can add some extra time to the query.

libname = upper('WORK') and memname = upper('MEANCPUABOTTOMFISH');