Hi everyone,
I have a large dataset which contains more than 150 variables.
I want to drop some of those variables which have a "flag" in the variable name, for example: Q15_flag.
I try to use (:), like drop :flag. but it did not work that way. Any help?
Thanks!
I would create a macro list from the dictionary columns.
proc sql noprint;
select name into :vars separated by ' '
from dictionary.columns
where upper(libname)='<ENTER>'
and upper(memname)='<ENTER>'
and upper(name) like '%FLAG';
quit;
data want;
set have (drop=&vars.;);
run;
There isn't a quick way to reference variables with suffixes, a better naming convention would be flag_q15, flag_q12 etc.
Then you could use flag:
Two alternatives,
1) change your naming convention in previous step
2) create a macro variable that has all the variable names that end in _flag an use that in your drop statement.
I would create a macro list from the dictionary columns.
proc sql noprint;
select name into :vars separated by ' '
from dictionary.columns
where upper(libname)='<ENTER>'
and upper(memname)='<ENTER>'
and upper(name) like '%FLAG';
quit;
data want;
set have (drop=&vars.;);
run;
I offer the following solution purely because my previous manager hated us using SQL - I think he just couldn't read it to be honest - so we had to come up with alternate solutions.
%macro test(dsn,suffix);
%global varlist;
/*This section of code opens the data sets and determines the number of variables*/
%let dsid=%sysfunc(open(&dsn));
%let nvars=%sysfunc(attrn(&dsid,nvars));
%let varlist=;
/*This section of code checks each variable name to see if the target suffix is found.
If it is, then the variable name is added to VARLIST*/
%do i=1 %to &nvars;
%let name=%sysfunc(varname(&dsid,&i));
%let chk=%sysfunc(reverse(&name));
%if %length(&name)>=%length(&suffix) %then %do;
%if %sysfunc(reverse(%substr(&chk,1,%length(&suffix))))=&suffix %then %let varlist=&varlist &name;
%end;
%end;
/*The following line of code closes the data set*/
%let rc=%sysfunc(close(&dsid));
%put &varlist;
data &dsn.;
set &dsn. (drop = &varlist);
run;
%mend;
%test(a,_flag);
Obviously this isn't as simple as using the SQL method, however it does produce a suitable outcome given the constraints imposed upon me.
Regards,
Scott
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.