BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
5 REPLIES 5
Flip
Fluorite | Level 6
proc sql;

select name into :vlist separated by " " from dictionary.columns
where libname = 'WORK' and memname = 'TEST';
quit;
%put &vlist;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
data_null__
Jade | Level 19
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]
deleted_user
Not applicable
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
data_null__
Jade | Level 19
> 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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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