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: 19,032

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

 %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,233

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

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: 19,032

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

 %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
  • 219 views
  • 0 likes
  • 3 in conversation