topic Re: Use existing Set and loop to read different Excel Tabs with dinamic outputs in SAS Programming

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

User12321 — Thu, 05 Jan 2017 01:42:07 GMT

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.

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

SASKiwi — Thu, 05 Jan 2017 02:28:30 GMT

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

data want;
  set have1 - have100;
run;

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

Reeza — Thu, 05 Jan 2017 02:37:16 GMT

%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#n1q1527d51eivsn1ob5hnz0yd1hx.htm

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

User12321 — Thu, 05 Jan 2017 03:35:17 GMT

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)

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

User12321 — Thu, 05 Jan 2017 03:47:33 GMT

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

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

User12321 — Thu, 05 Jan 2017 03:55:52 GMT

Looks like it may work with 9.2.

Thanks, I will try it tomorrow and will post update

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

User12321 — Thu, 05 Jan 2017 05:54:53 GMT

Thanks, it worked!