Export to excel is getting error in Stored Process

imdickson — Fri, 28 Apr 2017 03:26:47 GMT

Hi Guys, I have managed to create a report that contains custom HTML dropdown to select start month and end month.

Now, i want it to do "Export to Excel". However, I am getting error. Could anyone help me on this?

Here is my Export to Excel Code:

%GLOBAL DEBUG;
*ProcessBody;
	data _null_;
 	/* Set HTTP headers */
 	 rc = stpsrv_header('Content-type','application/vnd.ms-excel');
 	/* Prompt to SAVE or OPEN the attachment file named test.xls using Excel */
 	 rc = stpsrv_header('Content-disposition','attachment; filename=mgsd_DickReport_Monthly.xls'); 
 	/* OR */  
 	/* Open results directly to Excel (browser-dependent) */
  	/* rc = stpsrv_header('Content-disposition',"inline");  */
	run;


%global mydropdown;
	%let _ODSDEST=TAGSETS.MSOFFICE2K; 
/* Set ODS STYLE */
	%let _ODSSTYLE=seaside;


LIBNAME MGSRPT ODBC  DATAsrc=MGSD_PROD  SCHEMA=mgsd  USER=mgsdb  PASSWORD="censored" ;
PROC PRINTTO LOG="D:\Data\MGSD\RPT_STP\log\DickExcel.log" NEW;
run;

%STPBEGIN;
proc sql;
CONNECT TO ODBC(DATAsrc=MGSD_PROD  USER=mgsdb  PASSWORD="censored");
create table platformdata2 as(
select * from connection to ODBC (
select period,
Month_dt
	  ,Jerneh
      ,Lawit
      ,Bintang
      ,Damar
      ,PM8
      ,Larut_AG
      ,Guntong_D
      ,Palas
      ,GuE_Tabu
      ,GuE_Telok
      ,Guntong_A
      ,Larut_NAG
      ,Angsi
      ,Besar
      ,Bekok_A_AG
      ,Bekok_C_AG
      ,Tiong_AG
      ,Bekok_A_NAG
      ,Bekok_C_NAG
      ,Tiong_NAG
      ,Pulai_NAG
      ,Ledang
      ,Duyong
      ,Resak
      ,Tangga_Barat
      ,TCOT
      ,PM3
      ,Berantai
      ,Kamelia
      ,West_Natuna
      ,East_Piatu
      ,Muda,
	  Total_FG,
	  GPP_NOM

from mgsdprod.f_MGSDFinal
where datepart(mm,period) BETWEEN &start_dt and &end_dt
and Month_DT =%SQUOTE(&mydropdown)
;
));
%PUT &SQLXRC. &SQLXMSG.;
disconnect from ODBC;
quit;

/*and YEAR(period) = &param_CASE_RK*/
/*where datepart(mm,period) BETWEEN &start_dt and &end_dt;*/
/*and year(Period)=&PARAM_CASE_RK*/

data platformdata2 (rename=(RFFG=RF_FG_Avail RFGPP=RF_GPP_NOM Period=Date));/*fulldata */
set platformdata2;/*(drop=System_Total_FG)*/
/*newcolumn=RFFG;*/
/*rename (RFFG='RF FG Avail'
		RFGPP='RF GPP NOM';*/
if Total_FG < 1974 then do;
	RFFG=0.8;
end;
else if Total_FG < 2011 then do;
	RFFG=0.81;
end;
else if Total_FG < 2168 then do;
	RFFG=0.82;
end;
else if Total_FG < 2201 then do;
	RFFG=0.83;
end;
else if Total_FG < 2293 then do;
	RFFG=0.84;
end;
else RFFG = 0.85;



if GPP_Nom < 1599 then do;
	RFGPP=0.8;
end;
else if GPP_NOM < 1649 then do;
	RFGPP=0.81;
end;
else if GPP_NOM <1799 then do;
	RFGPP=0.82;
end;
else if GPP_NOM < 1849 then do;
	RFGPP=0.83;
end;
else if GPP_NOM < 1949 then do;
	RFGPP=0.84;
end;
else RFGPP=0.85;
drop GPP_NOM Total_FG MONTH_DT;
run;

title '**bleep** Report';
proc print data=platformdata2 noobs;
/*obs='No.'*/
run;


%STPEND;

After exporting to excel, it will prompt for error reading excel when i open it, and once i open the excel, it is showing blank inside.

I then look at the log file and here i got:

ODBC_7352: Prepared: on connection 7
select period, Month_dt ,Jerneh ,Lawit ,Bintang ,Damar ,PM8 ,Larut_AG ,Guntong_D ,Palas ,GuE_Tabu ,GuE_Telok ,Guntong_A ,Larut_NAG ,Angsi ,Besar ,Bekok_A_AG ,Bekok_C_AG ,Tiong_AG ,Bekok_A_NAG 
,Bekok_C_NAG ,Tiong_NAG ,Pulai_NAG ,Ledang ,Duyong ,Resak ,Tangga_Barat ,TCOT ,PM3 ,Berantai ,Kamelia ,West_Natuna ,East_Piatu ,Muda, Total_FG, GPP_NOM from mgsdprod.f_MGSDFinal where 
datepart(mm,period) BETWEEN &start_dt and &end_dt and Month_DT =%SQUOTE(MAR 2017) ;
 
ODBC: ROLLBACK performed on connection 7.
ERROR: CLI describe error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '&'. : [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be 
       prepared.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.



42000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '&'. : [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
73        +disconnect from ODBC;
NOTE: Statement not executed due to NOEXEC option.



ERROR: File WORK.PLATFORMDATA2.DATA does not exist.

Why am i getting PLATFORMDATA2 does not exist when my statement clearly creates PLATFORMDATA2 dataset.

Re: Export to excel is getting error in Stored Process

Reeza — Fri, 28 Apr 2017 03:45:43 GMT

Try your code without macro variables for the dates in the BETWEEN in the PROC SQL.

topic Re: Export to excel is getting error in Stored Process in SAS Programming

Export to excel is getting error in Stored Process

Re: Export to excel is getting error in Stored Process