I have a SAS data set A (just one column NAMES with 100 records) with list of all existing Excel tabs.
Suppose there is an existing macro %readtab(sheet) to read one tab/sheet from Excel.
I need help to create a loop that would read from set A each record (tab name), then using %readtab(sheet) read data from the Excel spreadsheet and put it into output file named as respective TAB.
I.e. after executing a loop, i should have 100 SAS sets with names like original Excel tabs.
Thanks.
%readtab(sheet) read data from the Excel spreadsheet and put it into output file named as respective TAB.
This should occur in the readtab macro - it should control/specify your output name.
No "loop" needed - look at Call Execute() it will call the macro for each value in your dataset. If you using a naming convention then you can append all the datasets at the end as indicated by @SASKiwi
There is an example in the documentation.
The easiest way to do this would be to number your tables like so:
data want;
set have1 - have100;
run;
I am not sure I follow.
I need Actuale names, and I do not know them in advance (i.e. set A is different every time I will run the program with new data)
%readtab(sheet) read data from the Excel spreadsheet and put it into output file named as respective TAB.
This should occur in the readtab macro - it should control/specify your output name.
No "loop" needed - look at Call Execute() it will call the macro for each value in your dataset. If you using a naming convention then you can append all the datasets at the end as indicated by @SASKiwi
There is an example in the documentation.
Thanks, I guess I should look at Example 2. It is quite close. I just need to figure out output sets. I probably need to put it inside of %readtab macro. Will this work with SAS 9.2?
I probably can get 9.4, but it takes long time to get something from IT at my place...
Looks like it may work with 9.2.
Thanks, I will try it tomorrow and will post update
Thanks, it worked!
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.