Hi,
I export to Excel using Proc Report and then export few more sheets as below.
If I rung the Proc Report till ods excel close; Excel file will open.
After I close Excel file and run the second part, the whole Excel file that contain Proc Report's result is deleted.
Can anyone help me to fix the problem?
Thank you.
HHCFX
*PART1==============================================================================;
ods listing close;
ods excel file="&export_folder\Allocation_&export_name..xlsx"
options ( sheet_interval = "none" sheet_name = "cost_by_company" );
ods excel options (sheet_interval = "proc" sheet_name = "cost_by_project");
proc report data=cost_by_project nowd
STYLE(Header)={background=CYAN foreground=black font_face="Calibri" font_size=12pt} /*top row*/
style(column)={font_face="Calibri" font_size=11pt};
define N/order noprint;
compute N ;
IF mod(N,4)=1 then call define(_row_,'style','style={background=LIGY}'); ELSE
IF mod(N,4)=2 then call define(_row_,'style','style={background=PWH}');
endcomp;
run;
ods excel close;
*PART2==============================================================================;
*export for checking purpose;
proc export data=_user_prj_hour_check dbms=xlsx
outfile="&export_folder\Allocation_&export_name..xlsx" replace;
sheet="CHECK_user_project";;run;
proc export data=user_hour_check dbms=xlsx
outfile="&export_folder\Allocation_&export_name..xlsx" replace;
sheet="CHECK_totalhours_with SA"; ;run;
Hi,
works for me too:
%let export_folder=C:\;
%let export_name=Table1;
*PART1==============================================================================;
ods listing close;
ods excel file="&export_folder\Allocation_&export_name..xlsx"
options ( sheet_interval = "none" sheet_name = "cost_by_company" );
ods excel options (sheet_interval = "proc" sheet_name = "cost_by_project");
proc report data=sashelp.class nowd
STYLE(Header)={background=CYAN foreground=black font_face="Calibri" font_size=12pt} /*top row*/
style(column)={font_face="Calibri" font_size=11pt};
define age/order noprint;
compute age ;
IF mod(age,4)=1 then call define(_row_,'style','style={background=LIGY}'); ELSE
IF mod(age,4)=2 then call define(_row_,'style','style={background=PWH}');
endcomp;
run;
ods excel close;
*PART2==============================================================================;
*export for checking purpose;
proc export data=sashelp.cars dbms=xlsx
outfile="&export_folder\Allocation_&export_name..xlsx" replace;
sheet="CHECK_user_project";;run;
proc export data=sashelp.demographics dbms=xlsx
outfile="&export_folder\Allocation_&export_name..xlsx" replace;
sheet="CHECK_totalhours_with SA"; ;run;
- Cheers -
@hhchenfx wrote:
I export to Excel using Proc Report and then export few more sheets as below.If I rung the Proc Report till ods excel close; Excel file will open.
After I close Excel file and run the second part, the whole Excel file that contain Proc Report's result is deleted.
What do you mean by "second part"?
In my original message, I have 2 section of code.
Works fine for me. Are you overwriting one of the sheets generated by the ODS EXCEL output with the PROC EXPORT output?
%let path=c:\downloads;
%let fname=&path\test1.xlsx;
ods excel file="&fname" ;
proc print data=sashelp.class;
run;
ods excel close;
proc export data=sashelp.class
dbms=xlsx file="&fname" replace ;
sheet="export";
run;
Hi,
works for me too:
%let export_folder=C:\;
%let export_name=Table1;
*PART1==============================================================================;
ods listing close;
ods excel file="&export_folder\Allocation_&export_name..xlsx"
options ( sheet_interval = "none" sheet_name = "cost_by_company" );
ods excel options (sheet_interval = "proc" sheet_name = "cost_by_project");
proc report data=sashelp.class nowd
STYLE(Header)={background=CYAN foreground=black font_face="Calibri" font_size=12pt} /*top row*/
style(column)={font_face="Calibri" font_size=11pt};
define age/order noprint;
compute age ;
IF mod(age,4)=1 then call define(_row_,'style','style={background=LIGY}'); ELSE
IF mod(age,4)=2 then call define(_row_,'style','style={background=PWH}');
endcomp;
run;
ods excel close;
*PART2==============================================================================;
*export for checking purpose;
proc export data=sashelp.cars dbms=xlsx
outfile="&export_folder\Allocation_&export_name..xlsx" replace;
sheet="CHECK_user_project";;run;
proc export data=sashelp.demographics dbms=xlsx
outfile="&export_folder\Allocation_&export_name..xlsx" replace;
sheet="CHECK_totalhours_with SA"; ;run;
- Cheers -
Thank you all for helping.
HHC
Please post the value of the automatic macro-variable SysVLong.
%put &=SysVLong;
Writes the value to the log.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.