Help using Base SAS procedures

Put all variable names into macro

Reply
Occasional Contributor
Posts: 7

Put all variable names into macro

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!

Contributor
Posts: 23

Re: Put all variable names into macro

Posted in reply to PeterPanPan

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' ;

Super User
Posts: 19,867

Re: Put all variable names into macro

Posted in reply to PeterPanPan

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;

Trusted Advisor
Posts: 1,932

Re: Put all variable names into macro

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

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

Super User
Posts: 19,867

Re: Put all variable names into macro

Posted in reply to PaigeMiller

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;

Super Contributor
Super Contributor
Posts: 444

Re: Put all variable names into macro

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

Respected Advisor
Posts: 3,156

Re: Put all variable names into macro

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

Trusted Advisor
Posts: 1,932

Re: Put all variable names into macro

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

Super User
Super User
Posts: 7,076

Re: Put all variable names into macro

Posted in reply to PeterPanPan

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;

Trusted Advisor
Posts: 1,932

Re: Put all variable names into macro

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

Super User
Super User
Posts: 7,076

Re: Put all variable names into macro

Posted in reply to PaigeMiller

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.

Ask a Question
Discussion stats
  • 10 replies
  • 1642 views
  • 0 likes
  • 7 in conversation