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.
Thanks for your valid comments.
I solved it through Ods tagsets.msofffice2k_x template which i have downloaded from sas website.
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.
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
Go back a step and generate one file instead of 10 then.
Or use DDE.
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.
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.
Thats assuming you can get the data into SAS. OP states he already has 10 Excel files which need to be combined.
Thanks for your valid comments.
I solved it through Ods tagsets.msofffice2k_x template which i have downloaded from sas website.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.