topic Re: Receiving error as Excel file is corrupted after executing the stored procedure through web port in Developers

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

v9dduhan — Wed, 15 Mar 2017 12:57:27 GMT

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 .

Re: Receiving error as Excel file is corrupted after executing the stored procedure through web port

RW9 — Wed, 15 Mar 2017 14:45:47 GMT

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;
...

Re: Receiving error as Excel file is corrupted after executing the stored procedure through web port

v9dduhan — Thu, 16 Mar 2017 10:55:11 GMT

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 ?

Re: Receiving error as Excel file is corrupted after executing the stored procedure through web port

Vince_SAS — Thu, 16 Mar 2017 12:07:16 GMT

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-format-that-the-file-name-extension-specifies

Vince DelGobbo

SAS R&D