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) = ¶m_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.
... View more