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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.