topic Re: PROC EXPORT OR ODS EXCEL in SAS Programming

PROC EXPORT OR ODS EXCEL

anjicam — Thu, 27 Dec 2018 09:23:47 GMT

Hi Everyone! Below is one of the challenge im facing now.

Existing scenario:
There is Excel template file placed in common folder. This template file contains two sheets (sheet1, sheet2).
Both the sheets contains only Variable Names but no records. Also this Template sheet have Footer information which will be displayed only when we do PRINT PREVIEW of excel.

Current SAS Code using DDE concept and this template file, writes work dataset data to 2nd sheet of template (not in Sheet1)
After exporting SAS data to excel using above DDE concept, this file will be stored to another Folder with another name with datestamp.

Now the output excel file contains
two sheets ( sheet1 - only variables ,no data ; Sheet2 - both variables and data records) .
Also contains Footer information.

Expected Scenario:
I need to achieve above scenario without USING DDE CONCEPT in SAS ENTERPRISE GUIDE (version: 9.04.01M2P07232014)
I can do this using PROC EXPORT or ODS EXCEL.
but the challange im facing is
1) In PROC EXPORT, i can export empty dataset data to sheet1 and data to sheet2 BUT i cant include/define FOOTER information.
2) In ODS EXCEL, i can include/define FOOTER information BUT i can't export empty dataset data to sheet1.

HOW TO ACHIEVE THIS. PLEASE HELP ME

Re: PROC EXPORT OR ODS EXCEL

Reeza — Thu, 27 Dec 2018 04:53:02 GMT

What version of SAS are you using? Option 2 should work with the latest version, but that may be 9.4 M6.

But you can also export your data to a template file that already has the footnote/titles you need or use VB to add it after the fact. T

Re: PROC EXPORT OR ODS EXCEL

anjicam — Thu, 27 Dec 2018 09:25:59 GMT

Hi, i m using SAS ENTERPRISE GUIDE (version: 9.04.01M2P07232014)

Re: PROC EXPORT OR ODS EXCEL

Cynthia_sas — Thu, 27 Dec 2018 16:30:44 GMT

Hi:
But you said that sheet one is not really empty -- it has the variable names, correct? Are the variable names in row 1? Then sheet 2 has the "real" data. Since you are using SAS 9.4 M2, I would recommend using ODS EXCEL. You'll have to write code to do what you want to do.
Cynthia

This is what I envision you are describing:

The above output was generated with this code:

Re: PROC EXPORT OR ODS EXCEL

anjicam — Thu, 27 Dec 2018 17:55:43 GMT

Thank you Cynthia. thank you somuch.

Re: PROC EXPORT OR ODS EXCEL

anjicam — Thu, 27 Dec 2018 18:13:42 GMT

Thanks for the answer. Could you please also tell how to have gridlines instead of whited out all the remaining rows n columns

Re: PROC EXPORT OR ODS EXCEL

Cynthia_sas — Thu, 27 Dec 2018 20:09:30 GMT

Hi:
I think there's a suboption to turn gridlines on INSIDE the table, if that's what you're referring to.

However, if you are referring to the fact that the gridlines outside of the table do not show, I believe that is Excel using the background from the style overlaid on top of the gridlines. If you want to make the background go away, I believe you need to change the style template.

Or, if you truly only want the data without ANY style (colors or fonts) at all, then move to PROC EXPORT or LIBNAME XLSX methods of getting your data from SAS into Excel.
Cynthia