DATA Step, Macro, Functions and more

how to combine multiple excel files into one single file with different sheets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

how to combine multiple excel files into one single file with different sheets

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.


Accepted Solutions
Solution
‎05-02-2015 03:48 AM
Frequent Contributor
Posts: 111

Re: how to combine multiple excel files into one single file with different sheets

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


All Replies
Super User
Posts: 6,972

Re: how to combine multiple excel files into one single file with different sheets

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 111

Re: how to combine multiple excel files into one single file with different sheets

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

Super User
Posts: 17,960

Re: how to combine multiple excel files into one single file with different sheets

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

Or use DDE.

Super User
Super User
Posts: 7,430

Re: how to combine multiple excel files into one single file with different sheets

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. 

Super Contributor
Super Contributor
Posts: 3,174

Re: how to combine multiple excel files into one single file with different sheets

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.

Super User
Super User
Posts: 7,430

Re: how to combine multiple excel files into one single file with different sheets

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

Solution
‎05-02-2015 03:48 AM
Frequent Contributor
Posts: 111

Re: how to combine multiple excel files into one single file with different sheets

Thanks for your valid comments.

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 1410 views
  • 0 likes
  • 5 in conversation