Help using Base SAS procedures

Macro with Variable-list from rows

Posts: 0

Macro with Variable-list from rows


I want to take some rows as my variable, whitch I put in a macro to Plot it.

plot (&yvar.)*xvar

But I dont know how I can make it more dynamic. Now I define my yvar manual for each plot (e.g. %plot (yvar=xyz_1 xyz_7 xyz_11 ...). How I can instruct SAS that it take all rows which begin with "xyz_" as yvar ? Maybe do you have an idea? Thanks?

Super User
Posts: 5,254

Re: Macro with Variable-list from rows

It's a bit unclear what you mean by "take some rows". My guess is that you mean variable/column name. In this case you can use DICTIONARY.COLUMNS, or data step functions to create desired macro variables/calls.

If you mean value within a row of data, it doesn't make sense to me. Please attach some sample data to describe your situation better.

Data never sleeps
Posts: 0

Re: Macro with Variable-list from rows

yes, I mean the name from the variable.

v1 v2 xyz_1 xyz_3 xyz_7
a a 2 3 1
a a 2 3 3

v1 v2 xyz_2 xyz_4 xyz_5
b b 1 2 1
b b 2 2 2

From this I want to create a varlist and give this to macrovaribale.
%let varlist1=columnames separated by space; in this case xyz_1 xyz_3 xyz_7
%let varlist2=columnames separated by space; in this case xyz_2 xyz_4 xyz_5
%plot (yvar=&varlist1);
%plot (yvar=&varlist2);

Super Contributor
Super Contributor
Posts: 3,174

Re: Macro with Variable-list from rows

Yes, the DICTIONARY.COLUMNS view will serve you well, using PROC SQL to generate a SAS macro variable using INTO with the appropriate WHERE clause.

Using the Google advanced search argument below, you will find SAS-hosted documentation and supplemental technical/conference papers on this type of topic, available at the SAS support website:

proc sql dictionary columns generate code

Scott Barry
SBBWorks, Inc.
Posts: 1,558

Re: Macro with Variable-list from rows

Scott's reply is the best in principle. However, accessing the dictionary can be very slow if you have many tables, especially if you have libraries pointing to RDBMS data.
To build a list from specific known tables, extracting table metadata directly is faster, if not as clean as querying the dictionary.
%macro makevarlist(table, prefix);
%let varlist=;
%let dsid=%sysfunc(open(&table));
%do i = 1 %to %sysfunc(attrn(&dsid,nvars));
%let varname=%sysfunc(varname(&dsid,&i));
%if %substr(&varname,1,3)=&prefix %then %let varlist=&varlist &varname;
%let dsid=%sysfunc(close(&dsid));
%put &varlist;
%makevarlist(sashelp.citiwk, WSP)
Ask a Question
Discussion stats
  • 4 replies
  • 4 in conversation