Use existing Set and loop to read different Excel Tabs with dinamic outputs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Use existing Set and loop to read different Excel Tabs with dinamic outputs

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.

 


Accepted Solutions
Solution
‎01-05-2017 12:55 AM
Super User
Posts: 23,754

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

Posted in reply to User12321
 %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.

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n1q1527d51eivsn1o...

 

 

 

View solution in original post


All Replies
Super User
Posts: 3,920

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

Posted in reply to User12321

The easiest way to do this would be to number your tables like so:

 

data want;
  set have1 - have100;
run;
Occasional Contributor
Posts: 10

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

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)

Solution
‎01-05-2017 12:55 AM
Super User
Posts: 23,754

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

Posted in reply to User12321
 %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.

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n1q1527d51eivsn1o...

 

 

 

Occasional Contributor
Posts: 10

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

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...

 

Occasional Contributor
Posts: 10

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

Looks like it may work with 9.2.

Thanks, I will try it tomorrow and will post update

Occasional Contributor
Posts: 10

Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs

Thanks, it worked!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 242 views
  • 0 likes
  • 3 in conversation