Hi Friends
Need some help here. Getting file in use error. Also there is gap between html body and table which needs to be reduced. Any help appreciated..
libname WBG '/sasdata/WBG/';
%let input_path = "/SASAnalytics/WBG/CorporateSupport/INWARD & IN-HOUSE CHEQUE REFERRAL/";
%let input_filename2 = REF_And_ESC_Pending_Report.xlsx;
proc import replace datafile= "/SASAnalytics/WBG/CorporateSupport/INWARD & IN-HOUSE CHEQUE REFERRAL/REF_And_ESC_Pending_Report.xlsx"
out=Cheque_referral
DBMS=XLSX;
range="REF And ESC Pending Report$A10:K30000";
GETNAMES=yes;
proc import replace datafile= "/SASAnalytics/WBG/CorporateSupport/INWARD & IN-HOUSE CHEQUE REFERRAL/RM_Team_Mapping.xlsx"
out=RM_Team_Mapping
DBMS=XLSX;
GETNAMES=yes;
proc sql;
delete from Cheque_referral where 'S.No.'n like '%End%';
delete from Cheque_referral where 'S.No.'n='';
run;
proc sql;
create table WBG.Cheque_referral as
select A.*,B.'Staff ID'n,
B.'Staff Name'n,
B.'Line manager'n,
B.Lead,
B.Staff_Email,
B.RM_Email from Cheque_referral
A left join RM_Team_Mapping B on A.RMCODE=B.'RM code'n
where SUBSTR(A.RMCODE,1,3) IN ('COR','GOV','GOV') and A.RMCODE in ('COR100063','COR100018');
run;
proc sql;
select distinct RM_Email into :toList separated by ' ' from WBG.Cheque_referral;
quit;
Data Cheque_referral_Email;
set WBG.Cheque_referral;
run;
proc sql;
select distinct Staff_Email,count(distinct Staff_Email) into :Staff_Email separated by '|',:c
from Cheque_referral_Email;
quit;
proc sql;
select distinct Staff_Email,count(distinct Staff_Email) into :Staff_Email separated by '|',:c
from Cheque_referral_Email;
quit;
proc sql;
select distinct RM_Email,count(distinct RM_Email) into :RM_Email separated by '|',:c
from Cheque_referral_Email;
quit;
%put &=Staff_Email;
%let cntsep1 = %sysfunc(count(&Staff_Email,|));
%put &=RM_Email;
%let cntsep2 = %sysfunc(count(&RM_Email,|));
%put &=cntsep2;
%let cntemail = %eval(&cntsep1+1);
%put &=cntemail;
/*
%macro t;
proc sql;
%do i =1 %to &cntemail;
%let EMAIL=%scan(&Staff_Email,&i,'|');
%put &=EMAIL;
%let RM_EMAIL=%scan(&RM_Email,&i,'|');
%put &=RM_EMAIL;
*/
%macro t;
proc sql;
%do i = 1 %to &cntemail;
%let EMAIL = %scan(&Staff_Email, &i, '|');
%let RM_EMAIL = %scan(&RM_Email, &i, '|');
data _null_;
filename mail email
From=('CIBGServiceQuality_SAS@adcb.com')
to=("&EMAIL")
cc=("&RM_Email")
subject="INWARD & IN-HOUSE CHEQUE REFERRAL NOTIFICATION"
attach=("/SASAnalytics/WBG/CorporateSupport/INWARD & IN-HOUSE CHEQUE REFERRAL/REF_And_ESC_Pending_Report.xlsx" content_type='xlsx')
type='text/html';
run;
ods html body=mail style=RTF options(vspace="NO");
style parskip / fontsize = 1pt;
proc odstext;
p "Dear &EMAIL," / style=[fontsize=11pt];
p 'Seeking your kind attention to the below pending cheque in Referral queue. The details are appended in below table for your easy reference. Kindly clear the same in case you wish to proceed.' / style=[fontsize=11pt];
run;
PROC REPORT DATA=WBG.Cheque_referral(where=(Staff_Email="&EMAIL")) nowd HEADLINE HEADSKIP
style(report)={background=whitex font_face="Arial" font_size=9pt just=left}
style(column)={background=white CELLHEIGHT=2.5% font_face="Arial" font_size=9pt just=left}
style(header)={foreground=white font_face="Arial" font_size=10pt just=left background=green};
columns 'S.No.'n QUEUENAME BUSINESSCATEGORY PROFITCENTER RMCODE RMNAME CUSTOMERNAME CUSTACCOUNTNO AMOUNT ODTODATPOSTING REFERREDREASON;
run;
Proc odstext;
p "Note: This is an automated email from SAS." / style=[fontsize=11pt];
p "Any questions please reach out to CIBG Service quality Team." / style=[fontsize=11pt];
p "Thankyou," / style=[fontsize=11pt];
run;
data _null_;
file mail;
put '<html>';
put '<body>';
put '<span style="font-size: 10pt; font-family: Arial, serif;">';
put '<br>';
put '<p>Dear &EMAIL,</p>';
put '<p>Seeking your kind attention to the below pending cheque in Referral queue. The details are appended in below table for your easy reference. Kindly clear the same in case you wish to proceed.<br>';
put '<br>';
put '<p>Note: This is an automated email from SAS.</p>';
put '<p>Any questions please reach out to CIBG Service quality Team.</p>';
put '<br>';
put '<p>Thanks and Regards,</p>';
put '<p>CIBG Service quality Team</p>';
put '</span>';
put '</body>';
put '</html>';
run;
ods html close;
%end;
%mend t;
%t;
