Help using Base SAS procedures

How to drop a series of variables?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

How to drop a series of variables?

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 (Smiley Happy, like drop :flag. but it did not work that way. Any help?

Thanks!


Accepted Solutions
Solution
‎06-27-2013 11:09 AM
Regular Contributor
Posts: 151

Re: How to drop a series of variables?

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.Smiley Wink;

run;

View solution in original post


All Replies
Super User
Posts: 19,851

Re: How to drop a series of variables?

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.

Solution
‎06-27-2013 11:09 AM
Regular Contributor
Posts: 151

Re: How to drop a series of variables?

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.Smiley Wink;

run;

Super Contributor
Posts: 297

Re: How to drop a series of variables?

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 368 views
  • 5 likes
  • 4 in conversation