excluding variables in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

excluding variables in proc sql

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;

 

 


Accepted Solutions
Solution
‎07-18-2018 04:13 PM
Super User
Posts: 6,935

Re: excluding variables in proc sql

Posted in reply to brainupgraded

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


All Replies
Solution
‎07-18-2018 04:13 PM
Super User
Posts: 6,935

Re: excluding variables in proc sql

Posted in reply to brainupgraded

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

Occasional Contributor
Posts: 11

Re: excluding variables in proc sql

Posted in reply to Astounding

This solution works, Thank you so much!

Respected Advisor
Posts: 3,281

Re: excluding variables in proc sql

[ Edited ]
Posted in reply to brainupgraded

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
Occasional Contributor
Posts: 11

Re: excluding variables in proc sql

Posted in reply to PaigeMiller
This solution works too. Thank you so much!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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