topic Re: excluding variables in proc sql in SAS Programming

excluding variables in proc sql

brainupgraded — Wed, 18 Jul 2018 19:28:09 GMT

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;

Re: excluding variables in proc sql

Astounding — Wed, 18 Jul 2018 19:40:22 GMT

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

Re: excluding variables in proc sql

PaigeMiller — Wed, 18 Jul 2018 19:45:47 GMT

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;

Re: excluding variables in proc sql

brainupgraded — Wed, 18 Jul 2018 20:13:44 GMT

This solution works, Thank you so much!

Re: excluding variables in proc sql

brainupgraded — Wed, 18 Jul 2018 20:14:45 GMT

This solution works too. Thank you so much!