BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
User12321
Obsidian | Level 7

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
 %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

6 REPLIES 6
SASKiwi
PROC Star

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

 

data want;
  set have1 - have100;
run;
User12321
Obsidian | Level 7

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)

Reeza
Super User
 %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...

 

 

 

User12321
Obsidian | Level 7

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

 

User12321
Obsidian | Level 7

Looks like it may work with 9.2.

Thanks, I will try it tomorrow and will post update

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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