Anyone figure out how to read the structure of an Excel xlsx file into SAS ODS statements? Rather than build the ODS, use the Excel file to create a template? It would be very handy when provided a template from a customer or govt agency to be able to recreate it within SAS.
@tomrvincent: Not sure exactly what you mean but, to me, it sounds like your 'want' is something that a group of us wanted a few years back and created a macro to accomplish the task in SAS.
Take a look at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export
One of the macro's capabilities is that it lets you specify an Excel template, or any Excel worksheet that you want to use as a template, and export all or part of a SAS dataset into specfic (but not pre-defined) ranges in the worksheet .. keeping all of the formatting, headings and formulas contained in the template.
HTH,
Art, CEO, AnalystFinder.com
@tomrvincent: Not sure exactly what you mean but, to me, it sounds like your 'want' is something that a group of us wanted a few years back and created a macro to accomplish the task in SAS.
Take a look at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export
One of the macro's capabilities is that it lets you specify an Excel template, or any Excel worksheet that you want to use as a template, and export all or part of a SAS dataset into specfic (but not pre-defined) ranges in the worksheet .. keeping all of the formatting, headings and formulas contained in the template.
HTH,
Art, CEO, AnalystFinder.com
Thanks! I think that will work out just fine!
Sorry, bit confused. Excel doesn't have "structure"? You can put anything anywhere. Secondly, ODS is the Output Delivery System, you dont put structure into it?
If you have a template Excel file and need to conform to that, then I would look at the method of getting data into it. So perhaps one idea is to dump data out to CSV and then use Excel VBA to read it in and put it where required in the Excel doc. Recreating what an Excel file looks like will be both very difficult and long winded.
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.