Store multiple values from a variable in different macros

[Frank_johannes]

Hi Everybody 

I want to store multiple values containing datasets names for the use in a loop to import multiple sas files? 

I have imported an excel file containing two rows: "Table" and "Select". For those rows where select is equal to "X" I want to create a macro containing the name in the table row? 

Example dataset:

Table   Select 

Table1   x 

Table2   x 

Table3 

Table4   x 

I want to create multiple macros containing the values "Table1", "Table2" and "Table3" for the use in a macro loop to import these three datafiles in SAS format 

Any suggestions?

Kind regards 

Frank 

[PaigeMiller]

You do not want macros. You want macro variables. These are not the same. Do not use the word "macro" to describe macro variables.

You say:

I want to create multiple macros containing the values "Table1", "Table2" and "Table3"

I assume you mean

I want to create multiple macro variables containing the values Table1, Table2  and  Table4 (should have table 4 not table 3, with no quotes)

data _null_;
    set have end=eof;
    n=0;
    if select="X" then do;
        n+1;
        call symputx(cats('macrovar',n),table);
    end;
    if eof then call symputx('n',n);
run;

%put &=macrovar1;
%put &=macrovar2;
%put &=macrovar3;
%put &=n;

[Frank_johannes]

Hi Paige, 

Thank you for your reply and corrections to this topic. 

My log says: WARNING: Apparent symbolic reference MACROVAR2 not resolved. 

I have typed:

proc import datafile= "&path\test.xlsx"
out= tables
dbms= xlsx
replace;
sheet= "Tables";
run;

data _null_;
set tables end=eof;
n=0;
if select="x" then do;
n+1;
call symputx(cats('macrovar',n),table);
end;
if eof then call symputx('n',n);
run;

%put &=macrovar1;
%put &=macrovar2;
%put &=macrovar;
%put &=n;

How can I loop through the macro variables in a datastep afterwards?

// Frank 

[Tom]

Macro variables will only be created for observations where the value of SELECT was a single lowercase x.

If it had an uppercase X or a space character followed by a lowercase x then it would not be "selected".

[PaigeMiller]

---

@Frank_johannes wrote:

Hi Paige, 

Thank you for your reply and corrections to this topic. 

My log says: WARNING: Apparent symbolic reference MACROVAR2 not resolved. 

I have typed:

proc import datafile= "&path\test.xlsx"
out= tables
dbms= xlsx
replace;
sheet= "Tables";
run;

data _null_;
set tables end=eof;
n=0;
if select="x" then do;
n+1;
call symputx(cats('macrovar',n),table);
end;
if eof then call symputx('n',n);
run;

%put &=macrovar1;
%put &=macrovar2;
%put &=macrovar;
%put &=n;

How can I loop through the macro variables in a datastep afterwards?

// Frank 

---

My mistake, the IF statement should test for lower case x

[Tom · Accepted solution]

Why do you need to store the data into macro variables?  Why not just use the data to drive the code creation directly.

data _null_;
  set example;
  where SELECT='X' ;
  call execute( ..... code generated using the value of TABLE ... );
run;

Or if you do need the list in macro variable why not just make ONE macro variable?

proc sql noprint;
select table into :tablelist separated by ' '
from example
where select='X'
;
%let ntables=&sqlobs;
quit;

If you really need to make multiple macro variable then use this SQL syntax.

proc sql noprint;
select table into :table1-
from example
where select='X'
;
%let ntables=&sqlobs;
quit;

[Reeza]

data example;
infile cards truncover;
input table $ select $;
cards;
one x
two x
three 
four x
five 
six x
;;;;;
run;

proc sql noprint;
select table into :table1- 
from example
where select = 'x';
quit;

options mprint;
%let numTables = &sqlobs;

%put &numTables;
%put &Table1.;
%put &Table2.;
%put &&table&numTables.;

Log:

 
 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 68         
 69         data example;
 70         infile cards truncover;
 71         input table $ select $;
 72         cards;
 
 NOTE: The data set WORK.EXAMPLE has 6 observations and 2 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              523.31k
       OS Memory           20644.00k
       Timestamp           01/12/2023 08:22:38 PM
       Step Count                        59  Switch Count  2
       Page Faults                       0
       Page Reclaims                     131
       Page Swaps                        0
       Voluntary Context Switches        10
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           264
       
 79         ;;;;;
 
 80         run;
 81         
 82         proc sql noprint;
 83         select table into :table1-
 84         from example
 85         where select = 'x';
 86         quit;
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              5456.81k
       OS Memory           25768.00k
       Timestamp           01/12/2023 08:22:38 PM
       Step Count                        60  Switch Count  0
       Page Faults                       0
       Page Reclaims                     72
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 
 87         
 88         options mprint;
 89         %let numTables = &sqlobs;
 90         
 91         %put &numTables;
 4
 92         %put &Table1.;
 one
 93         %put &Table2.;
 two
 94         %put &&table&numTables.;
 six
 95         
 96         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 106        

---

@Frank_johannes wrote:

Hi Everybody 

I want to store multiple values containing datasets names for the use in a loop to import multiple sas files? 

I have imported an excel file containing two rows: "Table" and "Select". For those rows where select is equal to "X" I want to create a macro containing the name in the table row? 

Example dataset:

Table   Select 

Table1   x 

Table2   x 

Table3 

Table4   x 

I want to create multiple macros containing the values "Table1", "Table2" and "Table3" for the use in a macro loop to import these three datafiles in SAS format 

Any suggestions?

Kind regards 

Frank 

---