/****************************************************************************************************************/
/* 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;
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;
Try this at the start of your code:
ODS _ALL_ CLOSE;
No. Just add this single line at the end of your code (after the last DATA step):
filename email clear;
thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.