Hello SAS users, I am trying to create a global macro for the list of variables in the dataset I have. There are over 400 variables in this dataset and I need to exclude the variables ending with "_TS" from the list. I tried to do that by using "reverse" and "substr" functions in the where cluase, but it doesn't work. I would appreciate any help to figure this out. Thanks. Here is my code. %macro rname(libname=,datanm=,range=_ALL_,pattern=%,separateby=%str( ));
data tmp;set &libname..&datanm.; /* create a temporary dataset */
run;
%if "&range." ^=%str() %then %do;
data tmp;set tmp;
keep &range.; /* keep the variables within "range" (e.g., character only, numeric only, variables within a specified range, etc.) */
run;
%end;
%global list_var; /* create a global macro */
%global list_rename; /* create a global macro */
%let list_var=%str(); /*reset the macro variable list_rename */
%let list_rename=%str(); /*reset the macro variable list_rename */
proc sql noprint;
select name into : list_var separated by ' ' /* creating a global macro for the list of variables */
from dictionary.columns
where libname=upcase("work") and memname = upcase("tmp") /* selecting a dataset to work on */
and name like "&pattern." escape '#' /* variable name patterns to select */
and reverse(substr(reverse(name), 1, 3))^="_TS";
select cats(name, '=n', name) into : list_rename separated by ' ' /* creating a global macro for the list of "sentences": the code creates a list of varname=nvarname separated by " " */
from dictionary.columns
where libname=upcase("work") and memname = upcase("tmp") /* selecting a dataset to work on */
and name like "&pattern." escape '#' /* variable name patterns to select */
and reverse(substr(reverse(name), 1, 3))^="_TS";
quit;
%mend;
... View more