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

Good day all,

i have one folder in E drive DATA. in that having 10 excel files with different formats.

now i want to combine all 10 excel files into one single excel file with different sheets.

i solved this through Excel-VBA. But i want to know is it possible through sas.

your valid comments are welcome.

1 ACCEPTED SOLUTION

Accepted Solutions
Ravikumarkummari
Quartz | Level 8

Thanks for your valid comments.

I solved it through Ods tagsets.msofffice2k_x template which i have downloaded from sas website.

View solution in original post

7 REPLIES 7
Kurt_Bremser
Super User

Why do it with SAS at all, once you solved it with the proper tool?

Don't fall into the trap to do everything with one tool. It will only limit your skills and let you create inefficient solutions.

To answer your question:

You can read all the Excel files with libname excel starting with SAS 9.4, and use the same to write to the .xlsx, if you only want to write the values.

You could also use ods tagsets.excelxp to apply formatting to the new Excel sheets.

Ravikumarkummari
Quartz | Level 8

Sorry i am having requirement for that. in sas only i have to combine all excel files into one excel file with multiple sheets.

Thanks

Reeza
Super User

Go back a step and generate one file instead of 10 then.

Or use DDE.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

What do you mean by combine 10 Excel files?  What do they contain.  Remember, Excel != SAS, I cannot repeat this enough.  SAS uses fixed structure tabular data, Excel uses a chuck it all in and mix it around approach.  The two are not naturally corss-functional.  *If* the Excel sheets just contain tabular data, then there is no reason not to read that into SAS, then output a new file with all the data.  However, if Excel doesn't contain tabular structured data, then its not going to be easy/at all.  As KurtBremser has mentioned, if you have solved the problem using VBA in Excel, then that's the way to go, otherwise you will be trying to shoehorn Excel garbage into SAS, then back out again.

IMO I would go back, and keep going back!, asking why this is necessary.  I have found that 90% of the time, if you keep pestering a vendor to deliver data in a proper format they will generally do it and this will make your life easier. 

sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10

Yes, you can - look at using ODS PACKAGE to generate a multi-sheet, XML-format (Excel compatible) document.  There are relevant-topic resources on the SAS.COM support site.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Thats assuming you can get the data into SAS.  OP states he already has 10 Excel files which need to be combined.

Ravikumarkummari
Quartz | Level 8

Thanks for your valid comments.

I solved it through Ods tagsets.msofffice2k_x template which i have downloaded from sas website.

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
  • 7 replies
  • 8812 views
  • 0 likes
  • 5 in conversation