SAS Studio automated code is emailing the same report three times. Why? Code is below....

sross002 — Fri, 07 Apr 2023 14:20:06 GMT

/****************************************************************************************************************/ /* THIS CODE IS INCLUDED IN BAEP_DAILY_SHELL.SAS. /****************************************************************************************************************/ /* This code inherits the following statements from BAEP_Daily_Shell.sas: */ /* *libname BAEPData "/sasdata/nonfinance/shared/data/30day/troop support/BAEP/Scheduled_Jobs/Data"; */ /* %let exptOutPath = %str(/sasdata/nonfinance/shared/data/30day/troop support/BAEP/Scheduled_Jobs/Export); */ /* %let outPath=%str(~/sas_pc_files_data/troop support/BAEP/Brianna Lake/Daily Runs/Output); */ /****************************************************************************************************************/ data _NULL_; call symputx("reportdate",put(intnx('day',today(),-1),mmddyyd10.)); /*MUST CHANGE NUMBER OF DAYS TO SUBTRACT FOR PRIOR MONTH'S EOM */ /*Formatted for humans.*/ run; %let outfile=%str(XXX Report_&reportdate..xlsx); /* %put &outfile.; */ %let currdate=%sysfunc(today()); /* No formatting applied yet. */ %let startdate=%sysfunc(intnx(month,&currdate,-1,B)); /* No formatting applied yet. */ %let enddate=%sysfunc(intnx(month,&currdate,-1,E)); /* No formatting applied yet. */ data _NULL_; startdate_=&startdate.; enddate_=&enddate.; call symput('startdate_edw',CATS(year(startdate_),put(month(startdate_),z2.),put(day(startdate_),z2.))); /* Formatted for EDW HANA. */ call symput('enddate_edw',CATS(year(enddate_),put(month(enddate_),z2.),put(day(enddate_),z2.))); /* Formatted for EDW HANA. */ call symput('startdate_hum',put(startdate_,mmddyy10.)); /* Formatted for humans. /* Formatted for humans. */ call symput('enddate_hum',put(enddate_,mmddyy10.)); call symputx("reportdate",put(intnx('day',today(),-1),mmddyyd10.)); /*Formatted for humans.*/ run; /****************************************************************************************************************/ /****************************************************************************************************************/ proc sql; connect to saphana as pvcon (&PVCONSTR.); create table test as select * from connection to pvcon ( select distinct substr(o30._BIC_BORGDNUM,1,6) as DoDAAC, b.M_BIC_BPRCGRCD5 as PGC, to_date(nullif(o30._BIC_BORDER_DT,'00000000'),'YYYYMMDD') as "Order Create Date", o30._BIC_BBSTNK as "Customer PO Number", o30._BIC_BORGDNUM as "Original Doc Num", o30._BIC_BSUFX_CD as SFX, b.M_BIC_B_NIIN_DESC_SH as Nomenclature, b.M_BIC_B_NSN as NSN, sum(o30.CONF_QTY) as Quantity, o30._BIC_BPRI_CD as "Pri Code", o30.BASE_UOM as "U/I", o30._BIC_BSUPPADR as "Supp Address", b.M_EANUPC as UPC, o30._BIC_BDMDPLANT as RIC, o30._BIC_BSUPP_ST as "Status", coalesce(to_date(nullif(o30.CH_ON,'00000000'),'YYYYMMDD'),to_date(nullif(o30.CREATEDON,'00000000'),'YYYYMMDD')) as "Status Date", sum(o30.NET_VALUE) as Price from "_SYS_BIC"."EDW.SQL/CV_OM_BOFHO30" o30 left join "_SYS_BIC"."EDW.SQL/CV_CS_MATERIAL" b on o30.MATERIAL= b.M_MATERIAL left join "_SYS_BIC"."EDW.SQL/CV_CS_ITEM_DETAIL" id on o30.MATERIAL = id.MATERIAL left join "_SYS_BIC"."EDW.SQL/CV_CS_PROFIT_CTR" pc on b.M_PROFIT_CTR = pc.PC_PROFIT_CTR where pc.PC_SUPPLY_CHAIN_DESC ='C&T' and o30._BIC_BSUPP_ST not in (/*'SS',*/'') and substr(o30._BIC_BORGDNUM,1,6) in ('Z47912') and o30._BIC_BEARCDTE='00000000' /* and o30._BIC_BSHIP_QTY='0' */ group by substr(o30._BIC_BORGDNUM,1,6), b.M_BIC_BPRCGRCD5, o30._BIC_BORGDNUM, o30._BIC_BSUFX_CD, b.M_BIC_B_NIIN_DESC_SH, b.M_BIC_B_NSN, o30._BIC_BPRI_CD, o30.BASE_UOM, o30._BIC_BSUPPADR, b.M_EANUPC, o30._BIC_BDMDPLANT, o30._BIC_BSUPP_ST, coalesce(to_date(nullif(o30.CH_ON,'00000000'),'YYYYMMDD'),to_date(nullif(o30.CREATEDON,'00000000'),'YYYYMMDD')), to_date(nullif(o30._BIC_BORDER_DT,'00000000'),'YYYYMMDD'), o30._BIC_BBSTNK ); disconnect from pvcon; quit; PROC EXPORT DATA=test (where=(Status not in ('B4','BQ','BR','BS','BF','C1','C2','C3','C4','C5','C6','C7','C8','CA','CB','CC','CD','CE','CF','CG','CH','CJ','CK','CL','CM','CN','CP','CQ','CR', 'CS','CT','CU','CV','CW','CX','CY','CZ','D1','D2','D3','D4','D5','D6','D8','CA','DB','DE','DF','DJ','DN','DP','DQ','DR','DY','PC','PG','PH','PJ'))) OUTFILE="&exptOutPath./&outfile." DBMS=xlsx REPLACE; SHEET="Open"; RUN; PROC EXPORT DATA=test (where=(Status in ('B4','BQ','BR','BS','BF','C1','C2','C3','C4','C5','C6','C7','C8','CA','CB','CC','CD','CE','CF','CG','CH','CJ','CK','CL','CM','CN','CP','CQ','CR', 'CS','CT','CU','CV','CW','CX','CY','CZ','D1','D2','D3','D4','D5','D6','D8','CA','DB','DE','DF','DJ','DN','DP','DQ','DR','DY','PC','PG','PH','PJ'))) OUTFILE="&exptOutPath./&outfile." DBMS=xlsx REPLACE; SHEET="Cancelled"; RUN; PROC EXPORT DATA=test (where=(Status in ('SS'))) OUTFILE="&exptOutPath./&outfile." DBMS=xlsx REPLACE; SHEET="SS"; RUN; filename mymail email to=( "NAME1 <EMAIL ADDRESS>" ) CC=( "NAME2 <EMAIL ADDRESS>" ) from=('Trp Spt RR&A Data Requests <TrpSptRR-ADataRequests@org.com>') subject="XXX Report &reportdate." attach=( "&exptOutPath/&outfile" CONTENT_TYPE="application/xlsx") ; data _null_; file mymail; put 'Dear All,'; put ' '; put "Please see the attached XXX report with data as of &reportdate.."; put ' '; put 'If you have any questions or concerns, please feel free to contact me.'; put 'I would be happy to assist any way that I can.'; put ' '; put 'Thank you,'; put 'XXX'; put ' '; put 'This email was sent from the SAS Grid Server.'; put "<&_SASPROGRAMFILE.>"; run;

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

ChrisHemedinger — Fri, 07 Apr 2023 14:23:13 GMT

I don't see anything in this code to trigger three emails, but keep in mind that SAS Studio generates its own ODS destinations (HTML, PDF, RTF -- depending on options). So you might want to use ODS CLOSE on those or change your preferences.

Also clear the fileref at the end of your code to ensure it's a closed destination.

filename email clear;

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

sross002 — Fri, 07 Apr 2023 14:35:58 GMT

Thanks, but where would I put the ODS CLOSE in the code?

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

ChrisHemedinger — Fri, 07 Apr 2023 14:57:09 GMT

Try this at the start of your code:

ODS _ALL_ CLOSE;

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

sross002 — Fri, 07 Apr 2023 15:04:03 GMT

Is the below code correct for the EMAIL CLEAR function....?
( "&exptOutPath/&outfile" CONTENT_TYPE="application/xlsx") email clear;

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

ChrisHemedinger — Fri, 07 Apr 2023 15:11:04 GMT

No. Just add this single line at the end of your code (after the last DATA step):

filename email clear;

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

sross002 — Fri, 07 Apr 2023 16:25:39 GMT

thank you!

topic Re: SAS Studio automated code is emailing the same report three times. Why? Code is below.... in SAS Programming

SAS Studio automated code is emailing the same report three times. Why? Code is below....

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....

Re: SAS Studio automated code is emailing the same report three times. Why? Code is below....