BookmarkSubscribeRSS Feed
Bill
Quartz | Level 8
(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
4 REPLIES 4
Peter_C
Rhodochrosite | Level 12
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
SASJedi
SAS Super FREQ
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;
Check out my Jedi SAS Tricks for SAS Users
Peter_C
Rhodochrosite | Level 12
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
SASJedi
SAS Super FREQ
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... 🙂
Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 17930 views
  • 0 likes
  • 3 in conversation