How to create multiple excel file links in Job Execution

GPatel — Wed, 12 Apr 2023 14:23:36 GMT

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;

topic How to create multiple excel file links in Job Execution in SAS Viya

How to create multiple excel file links in Job Execution