Receiving error as Excel file is corrupted after executing the stored procedure through web portal

[v9dduhan]

Hello SAS Experts,
Could any of you please help me out with this issue I'm facing?
1. I have a SAS stored process created and linked to web portal.
2. The SAS Stored process is required to generate an Excel file as output comtaining the needed data.
3. In order to get the desired out , I have to pre process the data with many conditions. Everything is correct whenever I have data in database and giving correct result in Excel.

But the problem comes whenever user gives incorrect input , I have coded it as it will give output in Excel as one record saying " no data available for this input number".

In all other report my logic is throwing this 1 record output in Excel, but in only in 1 report it's not giving desired output like other report.

In my observation, in other report , I just fetch the data using proc SQL and check the record count and if obs=0 then I place a proc report to put 1 record. But in this report as I have to pre process the data , so many datasets get created . And if record does not exist then error found in log , which is I think causing issue .

[RW9]

Sorry, its really hard to follow that.  Just put some error checking code around the block in question:

%macro do_report (parm=);
  %if &param. ne %then %do;
    ... report set
  %end;
  %else %do;
     ... report blank set
  %end;
...

[v9dduhan]

I used following code:

data _null_;

rc = stpsrv_header('Content-disposition','attachment; filename=test.xls');

rc = stpsrv_header('Content-disposition',"inline");

run;

PROC SQL NOPRINT;

SELECT COUNT(*) INTO: TOTALOBS

FROM HISTORY_DATA_TAB1;

QUIT;

%macro norecord;

/*%if &TOTALOBS eq 0 %then %do; */

data norecord1;

x= " NO RECORD FOUND FOR THIS CONTRACT ";

run;

TITLE "Basic Information Report";

PROC REPORT DATA= norecord1 MISSING;

COLUMN x

;

DEFINE X / DISPLAY "NO RECORD FOUND" FORMAT=$CHAR40. STYLE(column)={cellwidth=2.2in};

RUN;

/*%end; */

%mend;

%MACRO ALLRECORD1;

TITLE "Basic Information Report";

PROC REPORT DATA=HISTORY_DATA_TAB1 MISSING;

COLUMNS

COMPANY_CODE

CONTRACT_NUMBER

ISSUE_DATE

;

DEFINE COMPANY_CODE / DISPLAY "COMPANY CODE" FORMAT=$CHAR3.;

DEFINE CONTRACT_NUMBER / DISPLAY "CONTRACT NUMBER" FORMAT=$CHAR10.;

DEFINE ISSUE_DATE / DISPLAY "ISSUE DATE" FORMAT=$CHAR8.;

RUN;

%MEND;

%MACRO ALL1;

%IF &TOTALOBS NE 0 %THEN %DO;

%ALLRECORD1;

%END;

%ELSE %DO;

%norecord;

%END;

%MEND;

%ALL1;

Here the point to note is that when HISTORY_DATA_TAB1  dataset has record , it run successfully and give output to excel without any error, while HISTORY_DATA_TAB1  dataset does not have any record  it is throwing error while opening excel file saying that excel is corrupted.

This logic is working fine in all other reports i have created.

Can i get some help on this ?

[Vince_SAS]

You have two Content-disposition headers and it is unclear which one the browser honors.  You need only one.

Which ODS destination are you using?

Are you using STPBEGIN and STPEND?

What is the exact error that you get from Excel?  

Are there any errors, warnings, or messages in the SAS log?

Can you save and then post a copy of the file that gives the error.

Here is a common error that you encounter when the file extension does not match the file content:

Error opening file: "The file format differs from the format that the file name extension specifies"

https://support.microsoft.com/en-us/help/948615/error-opening-file-the-file-format-differs-from-the-...

Vince DelGobbo

SAS R&D