File in use error in SAS Email macro

SASUserRocks — Sat, 16 Mar 2024 16:00:37 GMT

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 '';

put ' ';

put 'Dear &EMAIL,';

put '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. ';

put ' ';

put 'Note: This is an automated email from SAS.';

put 'Any questions please reach out to CIBG Service quality Team.';

put ' ';

put 'Thanks and Regards,';

put 'CIBG Service quality Team';

put '';

put '</body>';

put '</html>';

run;

ods html close;

%end;

%mend t;

%t;

Re: File in use error in SAS Email macro

Kurt_Bremser — Sun, 17 Mar 2024 09:35:03 GMT

You have an unnecessary PROC SQL in your macro, but that's mostly cosmetic.

You have the ODS destination to the email file reference open while you try to use it in a DATA _NULL_ step. Do everything through ODS. As far as I can see, that DATA step only duplicates what you already do with ODSTEXT.

topic File in use error in SAS Email macro in SAS Programming

File in use error in SAS Email macro

Re: File in use error in SAS Email macro