BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ken_oy
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Keith
Obsidian | Level 7

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;

View solution in original post

3 REPLIES 3
Reeza
Super User

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.

Keith
Obsidian | Level 7

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;

Scott_Mitchell
Quartz | Level 8

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

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!

What is Bayesian Analysis?

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.

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
  • 3 replies
  • 1092 views
  • 5 likes
  • 4 in conversation