how to select rows with column value not in a macro list?
%macro changeName(&var_list);
proc sql noprint;
select cats(name,'=',case when type='num' then 'XN_' else 'XC_' end,name) into :renames separated by ' '
from dictionary.columns where libname='SASHELP' and memname='CLASS' and name not in(&var_list);
%put &renames;
data test;
set sashelp.class;
rename &renames;
run;
%mend();
%changeName(Age,Name);
Bit more complicated then:
%macro changename(dsin=,varlist=); proc sql; select cats(name,"=",name,"_want") into :tmp separated by " " from dictionary.columns where libname="WORK" and memname=upcase("&dsin.") and upcase(name) not in ( %do i=1 %to %sysfunc(countw(&varlist.,|)); %sysfunc(quote(%sysfunc(upcase(%scan(&varlist.,&i.,|))))) %end; ); quit; proc datasets lib=work nolist; modify &dsin.; rename &tmp.; quit; run; %mend changename; data want; set sashelp.class; run; %changename(dsin=want,varlist=age|name);
Take my suggestion from the other thread, and expand it:
data _null_;
set sashelp.vcolumn (where=(libname="LIBRARY" and memname = "DATASET")) end=eof;
length prefix $3;
if _n_ = 1 then call execute("
proc datasets library=LIBRARY nolist;
modify DATASET;
rename
");
if name not in (&var_list.)
then do;
if type = 'char'
then prefix = "XC_";
else prefix = "XN_";
call execute(trim(name !! '=' !! prefix !! trim(name) !! ' '));
end;
if eof then call execute("
;
quit;
");
run;
Edit: corrected in-list
Note that I prefer to use proc datasets for the renaming action because it does not rewrite the whole dataset, only the header page.
What does num_list look like? What is the end goal of the code?
If num_list is a space delimited list then there was this exact post yesterday:
However, depending on your process its likely you don't need any of this code. Also, again, please provide full information, test data, required output. Its written under the Post question button, and saves us having to guess.
Thanks, after looking at that, the simplest method is probably:
%macro changename(dsin=,dsout=,varlist=); data &dsout.; set &dsin.; rename %do i=1 %to %sysfunc(countw(&varlist.,|)); %scan(&varlist.,&i.,|)=%scan(&varlist.,&i.,|)_want %end; ; run; %mend changename; %changename(dsin=sashelp.class,dsout=want,varlist=age|name);
I use the | to delimit rather than a comma as the comma is used to divide paramters, so best avoided. I show as datastep here, but once you understand the above code, proc datasets should be used as that is quicker.
Edit, the same thing but with proc datasets, modifying in place:
%macro changename(dsin=,varlist=); proc datasets lib=work nolist; modify &dsin.; rename %do i=1 %to %sysfunc(countw(&varlist.,|)); %scan(&varlist.,&i.,|)=%scan(&varlist.,&i.,|)_want %end; ; quit; run; %mend changename; data want; set sashelp.class; run; %changename(dsin=want,varlist=age|name);
Bit more complicated then:
%macro changename(dsin=,varlist=); proc sql; select cats(name,"=",name,"_want") into :tmp separated by " " from dictionary.columns where libname="WORK" and memname=upcase("&dsin.") and upcase(name) not in ( %do i=1 %to %sysfunc(countw(&varlist.,|)); %sysfunc(quote(%sysfunc(upcase(%scan(&varlist.,&i.,|))))) %end; ); quit; proc datasets lib=work nolist; modify &dsin.; rename &tmp.; quit; run; %mend changename; data want; set sashelp.class; run; %changename(dsin=want,varlist=age|name);
@Geo- wrote:
handle variable is quite difficult sometime in sas
It should be a rather rare occurence. Data is fluent, while structure should be fixed. Changing column names are usually an indicator for poor design.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: