BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tomrvincent
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@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

3 REPLIES 3
art297
Opal | Level 21

@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
Rhodochrosite | Level 12

Thanks!  I think that will work out just fine!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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