I'm not sure I fully understand the question, but here are some ways that I use macro variables in horizontal lists, which is what that first PROC SQL statement is doing in your example. Lists of macro variables are really great things in SAS because you can use much less code to produce more charts or reports or other things. The key is being able to iterate over that list to get the output you want. When you have a horizontal list of macro variables, you can then iterate over that list with the %SCAN function. In this example, a horizontal list of macro variables (one value for each distinct value of STOCK in the sashelp.stocks data set) is used to plot multiple charts. I found this example somewhere... %macro graph_stocks;
proc sql noprint;
select distinct stock into :STOCK_LIST separated by '~'
from sashelp.stocks;
%let NUM_STOCKS = &sqlobs; /*This &NUM_STOCKS macro variable will store the number of macro variables in your horizontal list*/
quit;
%do I = 1 %to &Num_Stocks; /*Initialize the %DO loop for iterating over your list of macro variables*/
ods pdf file = "%scan(&STOCK_LIST, &I, '~').pdf";
proc sgplot data=sashelp.stocks;
where stock = "%scan(&STOCK_LIST, &I, '~')";
highlow x=date high=high low=low;
run;
ods pdf close;
%end;
%mend;
%graph_stocks(); In your message, you also talk about using a 'table' and not a macro variable. You can also make VERTICAL lists of macro variables in this manner below. Note that you do not use the SEPARATED BY syntax here. You will get back n macro variables for every n distinct levels of ORIGIN that exist. proc sql noprint;
select distinct origin
into :origin1- /*one macro variable is created for each distinct level of the the ORIGIN variable in SASHELP.CARS*/
from sashelp.cars
order by origin;
%let numorigins = &sqlobs;
quit;
%put &=origin1;
%put &=origin2;
%put &=sqlobs;
... View more