Export to Excel - Excel file get deleted

[hhchenfx]

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;

[PaigeMiller]

---

@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"?

[hhchenfx]

In my original message, I have 2 section of code.

[Tom]

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;

[Oligolas · Accepted solution]

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;

[hhchenfx]

Thank you all for helping.

HHC

[andreas_lds]

Please post the value of the automatic macro-variable SysVLong.

%put &=SysVLong;

Writes the value to the log.