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!
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 25. Read more here about why you should contribute and what is in it for you!
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.