"The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt"

[Jeff_DOC]

Good morning.

I have code that run and periodically generates no qualifying records. When this happens I'd like the user to receive a report with the same name but with just two lines stating no qualifying records exist for this reporting period. 

I have no idea what I'm doing wrong here. 

Thanks very much.

%END;
%ELSE %DO;
data _null_;
ods Excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx";
put "No qualifying records were found for the report period";
put "SECTION NAME";
run;
%END;

[ballardw]

---

@Jeff_DOC wrote:

Good morning.

I have code that run and periodically generates no qualifying records. When this happens I'd like the user to receive a report with the same name but with just two lines stating no qualifying records exist for this reporting period. 

I have no idea what I'm doing wrong here. 

Thanks very much.

%END;
%ELSE %DO;
data _null_;
ods Excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx";
put "No qualifying records were found for the report period";
put "SECTION NAME";
run;
%END;

---

Your posted code does not create any ODS output. So without any content your may have an issue. PUT writes to the log. You need more work to write to a file.

Missing an "Ods Excel Close;" statement likely but can't tell with partial code. Missing close means SAS is still attempting to use the file and opening in Excel will have issues.

[Jeff_DOC]

Well now that's interesting. Could you tell me how I would write a line(s) to the Excel file? I had no idea PUT only worked for the log. 

There is an entire section above that code that will generate output for the same named file (if there is any that is). This section is only used if no output records exist. Could I use PROC EXPORT and write lines to the file?

[Tom · Accepted solution]

You can use PUT to write to other places than the LOG.  But you need execute a FILE statement first.

%IF .... %THEN %DO;
....
%END;
%ELSE %DO;
ods excel file= "\\LOCATION\sasdata\Users\USERNAME\OCMS_No_PSC_&SYSDATE..xlsx";
data _null_;
  file print;
  put "No qualifying records were found for the report period";
  put "SECTION NAME";
run;
ods excel close;
%END;

[Jeff_DOC]

Worked like a charm (after correcting all my typos). Thanks very much.

[Reeza]

Or save the data to a data set and use PROC REPORT with the NOHEADER option to export.

[Jeff_DOC]

Another great idea. Thank you Reeza. I will keep both in mind.