- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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".
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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