topic Re: drop=_: variant in SAS Programming

drop=_: variant

Bill — Mon, 30 May 2011 20:37:39 GMT

(drop= _:) will drop all variables that begin with an underscore (_).

Is there a way to globally drop variables that end with a common set of characters, e.g.
_Flg? There are anywhere from 3 to 7 characters in front of the _Flg.

Thanks,
Bill

Re: drop=_: variant

Peter_C — Mon, 30 May 2011 22:33:28 GMT

there is no "common suffix" handler like the colon modifier indicating a common prefix.
The usual solution requires filling the list of columns into a macro variable by processing the output of proc contents or dictionary.columns. The former is my preference.
proc contents data= your_data_set noprint out= _data_ ; run;
proc sql noprint ;
select name into :list_of_cols separated by ' ' from &syslast
where lowcase(name) like '%_flg' ;
quit;
then you can use &list_of_cols

Of course this may be no help (if for example: when you do not have a previously defined data set)

good luck
peterC

Re: drop=_: variant

SASJedi — Wed, 01 Jun 2011 22:03:41 GMT

Here is a working custom macro "function" you can use to generate a list of variables ending in a suffix text pattern. It takes two parameters - the dataset name, and the suffix text:

%Macro EndsWith(DSN,Suffix);
%local dsid varlist rc whr lib ds;
/* Parameter Validation */
%if &suffix= %then %do;
%PUT ERROR: You must specify a suffix;
%GoTo EndMacro;
%end;
%if &DSN= %then %let DSN=&SYSLAST;
%let ds=%QSCAN(%QUPCASE(%SUPERQ(DSN)),2);
%if &ds= %then %do;
%let lib=WORK;
%let ds=%QUPCASE(%SUPERQ(DSN));
%end;
%else %let lib=%qscan(%QUPCASE(&DSN),1);
/* Get Variable Names */
%let whr=(WHERE=(LIBNAME="&lib" AND MEMNAME="&ds" AND UPCASE(NAME) like %STR(%')%nrstr(%%)%str(%QUPCASE(%SUPERQ(suffix))%')));
%let dsid=%sysfunc(open(sashelp.vcolumn&whr));
%if &dsid=0 %then %do;
%PUT ERROR: &DSN not opened;
%put ERROR- WHERE: &whr;
%PUT ERROR- &sysmsg;
%GoTo EndMacro;
%end;
%let rc=0;
%do %while (&rc=0);
%let rc=%sysfunc(fetch(&dsid));
%if &rc=0 %then %do;
%let varlist=&varlist %sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,NAME))));
%end;
%end;
&varlist
%Endmacro:
%let rc=%sysfunc(close(&dsid));
%mend;

Here are some usage examples:

/*Write a list of the variables that end in 't' to the log:*/

%PUT NOTE: Variables are %EndsWith(sashelp.class,t);

/*Drop variables that end in 't' from the input dataset:*/

data test;
set sashelp.class(drop=%EndsWith(sashelp.class,t));
run;

Re: drop=_: variant

Peter_C — Thu, 02 Jun 2011 15:53:07 GMT

SASjedi

thank you for posting the demo macro to collect from a filter of sashelp.vcolumn.
Have you compared the times? On my -ix SAS system the demo data step takes 11 seconds real and 1.8 cpu seconds.
SAShelp.vcolumn is a list of every column in every table in every library that is assigned in the SAS session, so sometimes it is just slow, and sometimes it is very slow. Because you open the view in data step functions there is no optimisation of the WHERE conditions. When you can open in SQL, filters on libname and memname make the performance a whole lot more acceptable.

peterC

Re: drop=_: variant

SASJedi — Thu, 02 Jun 2011 18:53:25 GMT

Because the macro utility uses only macro code (does not generate DATA step or PROC code), it can be leveraged to generate the variable list in-line with other SAS code, much like the macro functions that ship with SAS.

Querying dictionary.columns in PROC SQL is definitely more efficient in execution, but it is not possible to use the PROC SQL query directly in-line with other SAS code (the query must be "pre-executed") so that approach is somewhat less flexible.

It's good to have options, and in this case having a utility that can be used anywhere may override the performance considerations. And it was an irresistible opportunity to showcase the ability to access data in a purely macro environment... 🙂