topic Re: How to use macro parameter as keyword to select variables from a dataset in SAS Programming

How to use macro parameter as keyword to select variables from a dataset

tutu_ — Tue, 26 Oct 2021 21:41:22 GMT

I have a dataset that contains over 70 variables. For example, the variable name is like "dataname_parameter", I want to use '&parameter' 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);

Re: How to use macro parameter as keyword to select variables from a dataset

Reeza — Tue, 26 Oct 2021 21:44:35 GMT

Can you query the SASHELP.VCOLUMNS/dictionary.columns data set instead? Your requirements are a bit unclear but querying the dictionary tables seems to make this easier.

You CAN use macro variables inside double quotations perfectly fine by the way, just not single quotes.

Re: How to use macro parameter as keyword to select variables from a dataset

data_null__ — Tue, 26 Oct 2021 21:46:55 GMT

You can resolve &var using double-quotes instead of single-quotes.;

Re: How to use macro parameter as keyword to select variables from a dataset

Reeza — Tue, 26 Oct 2021 21:59:41 GMT

 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 '&parameter' 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);

Re: How to use macro parameter as keyword to select variables from a dataset

Tom — Wed, 27 Oct 2021 00:07:13 GMT

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;

Re: How to use macro parameter as keyword to select variables from a dataset

Reeza — Wed, 27 Oct 2021 00:08:53 GMT

NLITERAL is a great addition to the solution!

Re: How to use macro parameter as keyword to select variables from a dataset

tutu_ — Wed, 27 Oct 2021 02:36:01 GMT

Thank you! I tried your code and it went well!