topic Re: Export to Excel - Excel file get deleted in SAS Programming

Export to Excel - Excel file get deleted

hhchenfx — Wed, 03 Apr 2019 18:19:50 GMT

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;

Re: Export to Excel - Excel file get deleted

PaigeMiller — Wed, 03 Apr 2019 17:07:36 GMT

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

Re: Export to Excel - Excel file get deleted

hhchenfx — Wed, 03 Apr 2019 18:18:06 GMT

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

Re: Export to Excel - Excel file get deleted

Tom — Thu, 04 Apr 2019 04:41:54 GMT

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;

Re: Export to Excel - Excel file get deleted

Oligolas — Thu, 04 Apr 2019 06:26:51 GMT

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;

Re: Export to Excel - Excel file get deleted

andreas_lds — Thu, 04 Apr 2019 06:50:11 GMT

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

%put &=SysVLong;

Writes the value to the log.

Re: Export to Excel - Excel file get deleted

hhchenfx — Thu, 04 Apr 2019 17:11:58 GMT

Thank you all for helping.

HHC