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
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;
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;
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
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".
@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
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.