The SAS Output Delivery System and reporting techniques

read excel into ods

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

read excel into ods

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.


Accepted Solutions
Solution
‎06-14-2017 10:31 AM
PROC Star
Posts: 7,365

Re: read excel into ods

@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

View solution in original post


All Replies
Solution
‎06-14-2017 10:31 AM
PROC Star
Posts: 7,365

Re: read excel into ods

@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

Contributor
Posts: 21

Re: read excel into ods

Thanks!  I think that will work out just fine!

Super User
Super User
Posts: 7,430

Re: read excel into ods

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.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 198 views
  • 0 likes
  • 3 in conversation