BookmarkSubscribeRSS Feed
PeterPanPan
Calcite | Level 5

Hi there,

I am working on a macro, which is used to drop all variables with suffix "_dup". Now the problem is, how to put all variable names into this macro, since I have over 400 variables in this data set? Any ideas?

Thanks in advance!

10 REPLIES 10
MumSquared
Calcite | Level 5

I would have named the variables dup_XXXXX and then used

drop dup_: ;

But otherwise:

I have changed a piece of my code ..... to get the variable names in a dataset and the create another dataset changed accordingly.

* need to get a list of all variables and generate this code accordingly ;

  proc contents data=DataSetName out=DatasetContents;

  run;

  data _null_ ;          * No SAS data set is created ;

       set  DatasetContents end=lastobs;

       file  'W:\SAS_Temp\AutoCode1.txt' ;     * Output Text File ;

       if _n_ =1 then do;

             * for the start of the code ... ;

             put 'data NewDataSetName ; ';

             put '     set DataSetName; ';

             put '     drop ';

       end;

       * check if the name contains _dup ;

       if index(upcase(name),"_DUP")>0 then put ' name ' ;

       if lastobs then do;

            put '           ; ' ; * last variable ;

            put 'run ; ' ;

       end;

  run ;

* run the code we just wrote ;

  %include 'W:\SAS_Temp\AutoCode1.txt' ;

Reeza
Super User

You can hit the dictionary or SASHELP tables that list the metadata for each table.

The comparisons are case sensitive, and you may want to browse through SASHELP.VColumns so you understand what it's doing.

proc sql noprint;

select memname into :var_list separated by " "

from sashelp.vcolumn where libname="WORK" and memname="MY_TABLE" and upcase(name) like %_DUP" ;

quit;

PaigeMiller
Diamond | Level 26

You're missing a double-quote there, Reeza, the sql command should end with

... and memname="MY_TABLE" and upcase(name) like "%_DUP" ;

--
Paige Miller
Reeza
Super User

Yes it should Smiley Happy

proc sql noprint;

select memname into :var_list separated by " "

from sashelp.vcolumn where libname="WORK" and memname="MY_TABLE" and upcase(name) like "%_DUP" ;

quit;

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

Reeza,can you please explain why select memname and not select name ?

Haikuo
Onyx | Level 15

I believe it was a typo. It should be 'name'. In this case, 'memname' addresses name of the table, while 'name' contains the names of variables (columns).

Haikuo

PaigeMiller
Diamond | Level 26

I'll say it wasn't a typo

libname="WORK" finds all datasets in the proper directory in the dictionary table

memname="MY_TABLE" refers to the proper SAS data set in the dictionary table

upcase(name) like "%_DUP" refers to the column names in WORK.MY_TABLE and finds those that end with _DUP

--
Paige Miller
Tom
Super User Tom
Super User

There are two ways to get the list of variables in a dataset.  Use PROC CONTENTS and output to dataset. Or query the SAS metadata DICTIONARY.COLUMNS (also available as the view SASHELP.VCOLUMN).  Once you pick one of those for the source of the variable list then the easiest way to generate a DROP statement like that is to put the variable names into a macro variable using PROC SQL.

proc sql noprint ;

%let droplist=;

select name

  into :droplist separated by ' '

from dictionary.columns

where libname='MYLIB'

  and memname='MYDS'

  and upcase(name) like '%^_DUP' escape '^'

;

quit;

If your macro is copying the data then you can use this list in DROP statement or DROP= dataset option.

data want;

  set mylib.myds (drop=&droplist) ;

run;

PaigeMiller
Diamond | Level 26

Why do you use the ^ character in your where statement?

--
Paige Miller
Tom
Super User Tom
Super User

The LIKE operator uses underscore to match any single character.  So like '%_DUP' would match 'A_DUP' and 'ABDUP'.

By escaping it you can search for the underscore character itself.

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
  • 10 replies
  • 14521 views
  • 3 likes
  • 7 in conversation