topic Re: Put all variable names into macro in SAS Procedures

Put all variable names into macro

PeterPanPan — Wed, 30 Oct 2013 19:24:14 GMT

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!

Re: Put all variable names into macro

MumSquared — Wed, 30 Oct 2013 19:55:38 GMT

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

Re: Put all variable names into macro

Reeza — Wed, 30 Oct 2013 20:04:26 GMT

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;

Re: Put all variable names into macro

PaigeMiller — Wed, 30 Oct 2013 20:30:57 GMT

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

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

Re: Put all variable names into macro

Reeza — Wed, 30 Oct 2013 20:32:03 GMT

Yes it should

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;

Re: Put all variable names into macro

Tal — Fri, 01 Nov 2013 03:39:49 GMT

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

Re: Put all variable names into macro

Haikuo — Fri, 01 Nov 2013 12:58:55 GMT

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

Re: Put all variable names into macro

PaigeMiller — Fri, 01 Nov 2013 13:03:03 GMT

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

Re: Put all variable names into macro

Tom — Fri, 01 Nov 2013 13:04:45 GMT

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;

Re: Put all variable names into macro

PaigeMiller — Sat, 02 Nov 2013 15:44:14 GMT

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

Re: Put all variable names into macro

Tom — Sat, 02 Nov 2013 15:49:30 GMT

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.