Hi Friends
Please shed some light here.
First of all am getting File in use error. Also i am getting email in inbox , however , html body is missing. only grid is appearing . I want it together. Please assist in rectifying this code as i am sitting with this for 2 days..
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 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, '|');
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';
ods html file=mail;
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>';
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=cx5e2750 font_face="Arial" font_size=8pt just=left background=white};
columns 'S.No.'n QUEUENAME BUSINESSCATEGORY PROFITCENTER RMCODE RMNAME CUSTOMERNAME CUSTACCOUNTNO AMOUNT ODTODATPOSTING REFERREDREASON;
run;
data _null_;
file mail;
put '</span>';
put '</body>';
put '</html>';
run;
ods html close;
%end;
%mend t;
%t;
Click image to register for webinar
