I have following SAS code that creates two excel links using SAS VIYA JOB EXECUTION.
However, each excel link outputs the same (last) record of the dataset 'all'.
Need help/suggestions to fix this issue.
data all;
format inspectiondate date9.;
name='Johny'; Month_Year='01-2021'; Rpt_Dt='Inspection_Tasks_2021_01'; InspectionDate='01JAN2021'd;
output;
name='Jacob'; Month_Year='02-2021'; Rpt_Dt='Inspection_Tasks_2021_02'; InspectionDate='01FEB2021'd;
output;
run;
%global actlist Insp_Rpts ;
Proc SQL ;
Select distinct Month_year into: actlist separated by '~' from all ;
Select distinct Rpt_Dt into: Insp_Rpts separated by '~' from all ;
quit;
%put actlist = &actlist. ;
%put Insp_Rpts = &Insp_Rpts. ;
%MACRO RW;
Proc SQL ;
Select distinct Month_year into: actlist separated by '~' from all ;
Select distinct Rpt_Dt into: Insp_Rpts separated by '~' from all ;
quit;
%global actlist Insp_Rpts ;
%put actlist = &actlist. ;
%put Insp_Rpts = &Insp_Rpts. ;
%let i=1;
%do %while(%length(%scan(&Insp_Rpts,&i,"~")));
%let act=%scan(&Insp_Rpts,&i,"~");
Proc SQL NoPrint; Select min(InspectionDate) FORMAT=MMDDYY10. into:MIN_DATE SEPARATED by ' ' from all
where Rpt_Dt ="&act."; Quit; %put Min_DATE = &Min_DATE. ;
Proc SQL NoPrint; Select max(InspectionDate) FORMAT=MMDDYY10. into:MAX_DATE SEPARATED by ' ' from all
where Rpt_Dt ="&act."; Quit; %put Max_DATE = &Max_DATE. ;
Proc SQL NoPrint;
Select Min(inspectionDate) format=mmddyy10. into:Stdt SEPARATED by ' ' From work.all where Rpt_Dt ="&act.";;
Quit;
Proc SQL NoPrint;
Select Max(inspectionDate) format=mmddyy10. into:Enddt SEPARATED by ' ' From work.all where Rpt_Dt ="&act.";;
Quit;
%Put Stdt = &Stdt. EndDt = &Enddt. ;
%let i=%eval(&i+1);
ods escapechar='~';
ods _all_ close;
%put act_A = &act.; /* *************** */
filename f_xlxp filesrvc parenturi="&SYS_JES_JOB_URI"
name="NR.xml"
contenttype='application/vnd.ms-excel'
contentdisp='attachment; filename="&act.xlsx" ' ;
ods excel file=f_xlxp style=Plateau ;
%LET Curr_date = %left(%qsysfunc(date(),mmddyy10.)) ;
%let bold_style=~S={font_size=15pt font_weight=bold foreground=blue}~;
title1 j=left color=blue height=14pt "Sample Report ";
Proc print data=all(where=(Rpt_Dt ="&act.")) noobs label sumlabel style(header)={background=#33adff color=white just=center } ;
Title color=blue "&act";
run;
ods excel close;
ods _all_ close;
ods listing;
data _null_;
file _webout;
put '<HTML>';
put "<HEAD><TITLE>Sample Report</TITLE></HEAD>";
put '<BODY>';
put "<P> <font size= 4.5 bgcolor= Black color = Red >Sample Report can be downloaded by clicking on the link below.";
put "<P><font size= 4.5 bgcolor= Black color = Red > <a href=""&_FILESRVC_F_XLXP_URI/content"" target=""_SASDLResults"">Click here to download the report</a>";
put '</BODY>';
put '</HTML>';
Run;
%end;
%mend;
%RW;
