BookmarkSubscribeRSS Feed
anjicam
Calcite | Level 5

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 

1 REPLY 1
Sajid01
Meteorite | Level 14

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 959 views
  • 0 likes
  • 2 in conversation