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

[sross002]

/****************************************************************************************************************/

/* 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;

[ChrisHemedinger]

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;

[sross002]

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

[ChrisHemedinger]

Try this at the start of your code:

ODS _ALL_ CLOSE;

[sross002]

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

[ChrisHemedinger]

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

filename email clear;

[sross002]

thank you!