I have a script that loops through tables in a given library.
Once the table is grabbed there is a sub loop where it loops through column name and suppose to update them removing the "/" slashes.
All is good but does not read the columns properly when it reach such a column
the issue is in the line below where
¤tvar = table name dynamically picked.
&next_value. = column name original for example "ABC/D"
&yt = new column name that is "ABCD"
set LIBREF.¤tvar. (rename= (&next_value. = &yt.));
but it does not work!
It will work if you manually typed
All the best and much appreciated any help
Script below:
/* Step I */
LIBNAME libref 'F:\FOLDERWITHTABLES';
RUN;
/* Step II */
%let target_lib = 'LIBREF';
proc sql noprint;
select memname into :varlist separated by ' '
from dictionary.tables
where libname=&target_lib.
and memtype="DATA" and memname="TABLENAMEPICKED";
%let n=&sqlobs;
quit;
%MACRO abc;
/* First loop picking table names */
%do i=1 %to &n;
%let currentvar = %scan(&varlist,&i);
/* Second sub loop picking column names */
proc contents data=LIBREF.¤tvar. out=contents noprint; run;
proc sql;
select name into :varlist2 separated by ' ' from contents;
%let n2=&sqlobs;
quit;
%local i2 next_value x;
%let i2=1;
%do %while (%qscan(&varlist2, &i2, ' ') ne );
%let next_value = %qscan(&varlist2, &i2, ' ');
data _null_;
/*%let x = call symputx('x', translate(&next_value,'','/'));*/
%put &next_value || ' - ' || &i2;
/*call symputx('yt', translate(&next_value.,'_','/'));*/
%let yt = %sysfunc(translate(&next_value.,'_','/'));
%let counter = %sysfunc(findw(&next_value.,'/'));
%put &next_value || ' - ' || &i2 || ' - ' || &yt || ' - ' || &counter;
/* conversion */
data LIBREF.¤tvar.;
set LIBREF.¤tvar. (rename= (&next_value. = &yt.));
run;
run;
%let i2 = %eval(&i2 + 1);
%end;
%end;
%MEND abc;
%abc;
Proximate cause is likely that TRANSLATE is not doing what you think it is:
data example; x='ABC/D'; y=translate(x,'','/'); run;
Note that the Y value has a space where the / was. So the value of &yt is not a valid variable name.
So consider this code
data example; x='ABC/D'; y=compress(translate(x,'','/')); run;
A third piece is there isn't anything where you are building a string that looks like the "ABC/D"n. The name as stored in the dictionary table is simply ABC/D. You have to work to get a proper name-literal value.
And last, macro coding per se is not needed. You can create a data set with all of the data set names where the variable(s) have / in the name and then use a data step to either write Proc Datasets code or use call execute to create lines of code for proc data sets.
proc sql; create table work.rename as select memname, cats(quote(strip(name)),'n') as name, compress(translate(name,' ','/')) as newname from dictionary.columns where libname=upcase("&target_lib.") and index(name,'/')>0 order by memname ; quit; data _null_; set work.rename end=LastName; by memname; if _n_ = 1 then Call execute ("Proc datasets library=&target_lib. nodetails nolist;"); if first.memname then do; Call execute ("modify "||memname||";"); Call execute ("rename ") ; end; Call execute(catx(' ',name,' = ',newname)) ; if LastName then do; Call execute (";") ; Call execute ("quit;"); end; run;
Proc datasets can change variable names, formats or labels for a data set or variables. When you use a data step for that purpose you have to read the entire data which could take significant time, not mention in this case if you have multiple problem variables you reread that set for each variable, terribly inefficient.
My suggestion is to put this before your macro call:
options mprint;
This helps you to see the generated code by your macro abc in the log. Once you see the generated code, you can debug easier.
You can eliminate the looping and just generate the OLD=NEW pairs from the metadata.
For example here is code that just generates PROC DATASETS code to rename the variables in place.
%macro abc(libref);
proc contents data=&libref.._all_ noprint
out=contents(keep=libname memname name)
;
run;
data _null_;
set contents;
by libname memname ;
where indexc(name,'/');
if first.libname then call execute(cats('proc datasets nolist lib=',libname,';'));
if first.memname then call execute(catx(' ','modify',memname,';'));
call execute(catx(' ','rename',catx('=',nliteral(name),nliteral(compress(name,'/')),';'));
if last.memname then call execute('run;');
if last.libname then call execute('quit;');
run;
%mend abc;
libname mylib 'path to my datasets';
%abc(mylib);
You could modify it to generate new datasets if what instead.
@lyudmilpetrov wrote:
Hi Tom,
Thank you so much,
Reading that it seems super genius, I just started using SAS few days ago and still learning but have experience in other languages, so still learning the ways of SAS.
I will try it later and let you know if all good.
All the best,
Lyudmil
Learn how to use SAS before trying to do things with the macro language. It is really hard to write a program that generates code when you don't know what code you should be generating.
For example where did those goofy names come from? Perhaps they were column headers on a file you imported? If you set the options VALIDVARNAME to V7 before the PROC IMPORT step then it should convert that strange headers into valid variable names for you automatically and there is nothing to rename.
If you've got a programming background then reading through "SAS® 9.4 Language Reference: Concepts, Sixth Edition" is likely what will you fastest familiarise with the SAS intrinsics.
Reading that it seems super genius, I just started using SAS few days ago and still learning but have experience in other languages, so still learning the ways of SAS.
If you've got a programming background then reading through "SAS® 9.4 Language Reference: Concepts, Sixth Edition" is likely what will you fastest familiarise with the SAS intrinsics.
Hi Tom,
Thanks for the shared knowledge I run it and it produces the following error
79
45 ! last.libname then call execute('quit
ERROR 79-322: Expecting a ).
I am not sure why is that.
Thank you,
So fix it and rerun it.
It you want more help you need to show the full log of the code you actually ran. Make sure to paste using the Insert Code pop-up window so that formatting of the log is preserved.
Proximate cause is likely that TRANSLATE is not doing what you think it is:
data example; x='ABC/D'; y=translate(x,'','/'); run;
Note that the Y value has a space where the / was. So the value of &yt is not a valid variable name.
So consider this code
data example; x='ABC/D'; y=compress(translate(x,'','/')); run;
A third piece is there isn't anything where you are building a string that looks like the "ABC/D"n. The name as stored in the dictionary table is simply ABC/D. You have to work to get a proper name-literal value.
And last, macro coding per se is not needed. You can create a data set with all of the data set names where the variable(s) have / in the name and then use a data step to either write Proc Datasets code or use call execute to create lines of code for proc data sets.
proc sql; create table work.rename as select memname, cats(quote(strip(name)),'n') as name, compress(translate(name,' ','/')) as newname from dictionary.columns where libname=upcase("&target_lib.") and index(name,'/')>0 order by memname ; quit; data _null_; set work.rename end=LastName; by memname; if _n_ = 1 then Call execute ("Proc datasets library=&target_lib. nodetails nolist;"); if first.memname then do; Call execute ("modify "||memname||";"); Call execute ("rename ") ; end; Call execute(catx(' ',name,' = ',newname)) ; if LastName then do; Call execute (";") ; Call execute ("quit;"); end; run;
Proc datasets can change variable names, formats or labels for a data set or variables. When you use a data step for that purpose you have to read the entire data which could take significant time, not mention in this case if you have multiple problem variables you reread that set for each variable, terribly inefficient.
Thanks a lot, I accepted your response as solution works perfectly.
All the best,
Lyudmil
Make sure to use delimiters in your lists of names that do not appear in the names. If your names contain / then they might also contain spaces.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.