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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.