topic Re: read excel into ods in ODS and Base Reporting

read excel into ods

tomrvincent — Tue, 13 Jun 2017 14:14:51 GMT

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.

Re: read excel into ods

art297 — Tue, 13 Jun 2017 14:38:21 GMT

@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

Re: read excel into ods

RW9 — Tue, 13 Jun 2017 14:40:42 GMT

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.

Re: read excel into ods

tomrvincent — Wed, 14 Jun 2017 14:30:59 GMT

Thanks! I think that will work out just fine!