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;
I can't say whether this is the only issue, but it's a key issue. This comparison is incorrect:
and reverse(substr(reverse(name), 1, 3))^="_TS";
The problem is that NAME is actually 32 characters long. Most of the time, the last three characters are blank rather than "_TS". So you will need to get rid of some blanks.
and reverse(substr(left(reverse(name)), 1, 3))^="_TS";
If you think it would be easier to read, you could get rid of the final REVERSE:
and substr(left(reverse(name)), 1, 3)^="ST_";
I can't say whether this is the only issue, but it's a key issue. This comparison is incorrect:
and reverse(substr(reverse(name), 1, 3))^="_TS";
The problem is that NAME is actually 32 characters long. Most of the time, the last three characters are blank rather than "_TS". So you will need to get rid of some blanks.
and reverse(substr(left(reverse(name)), 1, 3))^="_TS";
If you think it would be easier to read, you could get rid of the final REVERSE:
and substr(left(reverse(name)), 1, 3)^="ST_";
This solution works, Thank you so much!
UNTESTED CODE
proc sql noprint;
select distinct name into :list_var separated by ' ' from dictionary.columns
where upcase(libname)='WORK' and upcase(memname)='TMP'
and find(reverse(trim(name)),'ST_','i')^=1;
quit;
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.