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