I have a dataset that contains over 70 variables. For example, the variable name is like "dataname_parameter", I want to use '¶meter' to select variables from the dataset in macro. I know my code is not correct, can't use &var inside quotation mark but I don't know how to fix it.
%macro select(var);
filename tmp temp;
data _null_;
file tmp;
if 0 then set data1;
length varname $32;
do until (varname='varname');
call vnext(varname);
if index(varname,'&var') then put varname;
end;
run;
data want;
set data1;
keep
%include tmp;
;
run;
%mend;
%select(SNO);
options mprint symbolgen;
%macro select(lib =, ds_in=, pattern=, ds_out=);
proc sql noprint;
select name into :var_list separated by ' '
from dictionary.columns
where libname = upcase("&lib")
and memname = upcase("&ds_in")
and find(name, "&pattern.", 'it')>0;
quit;
data &ds_out;
set &lib..&ds_in.;
keep &var_list.;
run;
%mend;
%select(lib=sashelp, ds_in=cars, pattern=mpg, ds_out=demo);
A more robust solution in the long run. Depends on how generic you need it to be.
https://gist.github.com/statgeek/8f90f5c545e01c48e4aaafb82d1a8ae8
@tutu_ wrote:
I have a dataset that contains over 70 variables. For example, the variable name is like "dataname_parameter", I want to use '¶meter' to select variables from the dataset in macro. I know my code is not correct, can't use &var inside quotation mark but I don't know how to fix it.
%macro select(var);
filename tmp temp;data _null_;
file tmp;
if 0 then set data1;
length varname $32;
do until (varname='varname');
call vnext(varname);
if index(varname,'&var') then put varname;
end;
run;
data want;
set data1;
keep
%include tmp;
;
run;
%mend;
%select(SNO);
The macro processor ignores text inside of single quotes. You need to use double quote characters instead.
But your code is way too complicated. Plus you probably want to ignore the case of the variable names and protect yourself against non-standard variable names.
%macro select(var);
proc sql noprint;
select nliteral(name) into :var separated by ' '
from dictionary.columns
where libname='WORK' and memname='DATA1'
and find(name,"&var",'i')
;
quit;
data want;
set data1;
keep &var ;
run;
%mend;
Thank you! I tried your code and it went well!
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: