BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brainupgraded
Obsidian | Level 7

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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_";

View solution in original post

4 REPLIES 4
Astounding
PROC Star

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_";

brainupgraded
Obsidian | Level 7

This solution works, Thank you so much!

PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
brainupgraded
Obsidian | Level 7
This solution works too. Thank you so much!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2233 views
  • 2 likes
  • 3 in conversation