topic Re: dynamically created variable list in SAS Programming

dynamically created variable list

deleted_user — Fri, 21 Aug 2009 16:16:27 GMT

How do I dynamically specify a list of variables to keep in a dataset?

I've created a file using proc contents that contains a list of variables. I want to use this list in a data step but each time I run the program the list can change.

For example I want to do something like this;

data test (keep=v1 v2 v3 v4 v5); set old; or I want to dynamically specify the columns in a proc report ie column v1 v2 v3 v4 v5.

data set old may contain 10 variables but I only want the first 5. The list might be totally different next time so hard coding v1 to v5 would not work.

Re: dynamically created variable list

Flip — Fri, 21 Aug 2009 16:37:03 GMT

proc sql;

select name into :vlist separated by " " from dictionary.columns
where libname = 'WORK' and memname = 'TEST';
quit;
%put &vlist;

Re: dynamically created variable list

sbb — Fri, 21 Aug 2009 16:38:19 GMT

Explore using the DICTIONARY TABLES with PROC SQL to build a SAS macro variable with the required variable(s) you desire. Use this Google advanced search argument to find related DOC and technical articles/papers on the SAS support website at http://support.sas.com/ :

proc sql dictionary tables site:sas.com

Scott Barry
SBBWorks, Inc.

Re: dynamically created variable list

data_null__ — Fri, 21 Aug 2009 16:48:10 GMT

You probably don't need to create a new data set just get the list of names. You want the 5 left most by position in the data set right?

[pre]
proc sql noprint nowarn outobs=3 /*or 5*/;
select name into :names separated by ' '
from dictionary.columns
where libname eq 'SASHELP' and memname eq 'CLASS'
order by varnum
;

quit;
run;
%put NOTE: NAMES=&names;

proc report data=sashelp.class nowd;
columns &names;
run;
[/pre]

Re: dynamically created variable list

deleted_user — Fri, 21 Aug 2009 18:24:12 GMT

Ah figured out why it was not working for me

the sql is case sensitive and I had typed in the name of my dataset in lower case.

Unfortunately, I need to use this list to do things like create define statements for proc report. I really need the list stored in a macro variable that can be referenced

Re: dynamically created variable list

data_null__ — Fri, 21 Aug 2009 19:55:31 GMT

> Unfortunately, I need to use this list to do things
> like create define statements for proc report. I
> really need the list stored in a macro variable that
> can be referenced

Unfortunate? What information do you need to supply for the DEFINE statements, you may be able to write generic define statements. Consider the following...

[pre]
proc sql noprint nowarn outobs=5 /*or 5*/;
select name into :names separated by ' '
from dictionary.columns
where libname eq 'SASHELP' and memname eq 'CLASS'
order by varnum
;

quit;
run;
%put NOTE: NAMES=&names;

proc report data=sashelp.class(keep=&names) nowd list;
columns &names;
define _all_ / group;
define _numeric_ / format=comma12.;
define _character_ / format=$10.;
run;
[/pre]

Or if that is inadequate you could "code gen" define statements in a way similar to the variable list &NAMES. A bit more complicated by doable.