Proc export or ODS EXCEL

anjicam — Thu, 27 Dec 2018 03:31:33 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.
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

Sajid01 — Tue, 01 Jan 2019 20:15:58 GMT

Hello

This does all what you want

/*https://communities.sas.com/t5/SAS-Enterprise-Guide/Proc-export-or-ODS-EXCEL/m-p/523599#M32752*/
/* Importing sheet */
proc import file="/folders/myfolders/Excel/file1_01JAN2019.xlsx" out=work.test replace dbms=xlsx;
sheet="sheet1";
run;
proc import file="/folders/myfolders/Excel/file1_01JAN2019.xlsx" out=work.test2 replace dbms=xlsx;
sheet="sheet2";
run;
/* your code steps here*/


/*writing to sheets*/
ods excel file="/folders/myfolders/Excel/file2_01JAN2019.xlsx" options(sheet_interval=
"proc"
sheet_name="sheet1"
embedded_titles='on'
embedded_footnotes='on');
Footnote "Footer1";
proc print data=work.test noobs;
run;

ods excel options (sheet_name="sheet2");
Footnote "Footer2";
proc print data=sashelp.class noobs;
run;

ods excel close;

topic Re: Proc export or ODS EXCEL in SAS Enterprise Guide

Proc export or ODS EXCEL

Re: Proc export or ODS EXCEL