topic Re: Macro with Variable-list from rows in SAS Procedures

Macro with Variable-list from rows

deleted_user — Wed, 17 Jun 2009 09:49:19 GMT

Hi,

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?

Lex

Re: Macro with Variable-list from rows

LinusH — Wed, 17 Jun 2009 11:52:54 GMT

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.

/Linus

Re: Macro with Variable-list from rows

deleted_user — Wed, 17 Jun 2009 12:33:58 GMT

yes, I mean the name from the variable.

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

(2)
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);

Lex

Re: Macro with Variable-list from rows

sbb — Wed, 17 Jun 2009 13:18:01 GMT

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 http://support.sas.com/ website:

proc sql dictionary columns generate code site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Macro with Variable-list from rows

ChrisNZ — Wed, 17 Jun 2009 23:34:07 GMT

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.
[pre]
%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;
%end;
%let dsid=%sysfunc(close(&dsid));
%put &varlist;
%mend;
%makevarlist(sashelp.citiwk, WSP)