Get Data from SAS Macro (list of values) to SAS table (column)

0
votes

I am trying to create a SAS table from Macro variable using PROC SQL:

I have a list of value saved in a macro variable : %let l=1,2,3;

I want to create a SAS table with a column containing the values of the macro variable : 1 2 3

Thank you very much for your help. Sincerely, Abdeljalil

3
sqlmacrossas
SQL does not have a looping construct (such as shown by @DomPazz). Do you really need SQL ? If so, your code will need a code generator (a macro) that constructs a values clause for use in a SQL insert statement. – Richard
How did you make the macro variable? If you made it from a dataset why not just use that dataset? – Tom
support.sas.com/kb/39/605.html Please try the solution outlined here. – Reeza

3 Answers

0
votes

you should so some effort to solving this yourself.

Put the values into a string, parse the string and output the values you would like.

%let l=1,2,3;

data want;
str = "&l";
do i=1 to countw(str,',');
   value = input(scan(str,i,","),best.);
   output;
end;

/*drop other variables if you want*/
drop str i;
run;
0
votes

Something like this?

%let age=%str(12,13,15);
proc sql;
    select * from sashelp.class where age in (&age);
quit;
0
votes

You have a data set that contains a list of names and you want to place these names into a macro variable for later use. That will work as long as the macro variable does not go beyond the 64K limit. If the value hits this limit, then you can use macro processing to retrieve the names from the data set. Since a macro definition does not have the 64K restriction, it can be used to create the list for you. In the sample code on the Full Code tab, we have a list of names that we want to use on an INPUT statement along with a given informat. This sample demonstrates how to create the list without having to use a macro variable.

data one;                                                                                                                               
   input name $;                                                                                                                           
   datalines;                                                                                                                                   
abc                                                                                                                                     
def                                                                                                                                     
ghi                                                                                                                                     
; 
run;                                                                                                                                      

%macro test;                                                                                                                            
  %let dsid=%sysfunc(open(one));                                                                                                         
  %let cnt=%sysfunc(attrn(&dsid,nobs));                                                                                                  
    %do i=1 %to &cnt;                                                                                                                     
      %let rc=%sysfunc(fetchobs(&dsid,&i));                                                                                               
      %cmpres(%sysfunc(getvarc(&dsid,%sysfunc(varnum(&dsid,name))))) $4.                                                                      
    %end;                                                                                                                                 
  %let rc=%sysfunc(close(&dsid));                                                                                                        
%mend test;                                                                                                                             

/** Using %PUT to see outcome **/
/** %test could be used on an INPUT statement **/                                                                                                                                 
%put %test;

source: http://support.sas.com/kb/39/605.html