I lose the formatting of my two datasets when I email it as one Excel file.... How can I retain the formatting in the final product that gets emailed? Any help in retaining the formatted fields is greatly appreciated. Thanks in advance.
Here is the code:
/*UPDATE FY AND QTR*/
%let QDate = 'FY23 QTR4';
/*UPDATE FY AND QTR*/
%let exptOutPath = %str(~/sasviyadata/LOCATION);
%let outfile=%str(Capital_Equipment_VA_Shared Obligations.xlsx);
libname xl xlsx "&exptOutPath/&outfile.";
proc sql ;
connect to saphana as pvcon (&PVCONSTR.);
create table wo_MODS as select * from connection to pvcon
(SELECT
/* Case when substring(PO02._BIC_BCONTRACT,7,1) <> '-' then LEFT(PO02._BIC_BCONTRACT,6) || '-' || substring (PO02._BIC_BCONTRACT,7,2) || '-' || RIGHT(PO02._BIC_BCONTRACT,4) */
/* else PO02._BIC_BCONTRACT end as Contract /*this code adds dashes to contracts that don't have dashes*/
PO02._BIC_BPIIN_13 as Contract,
PO02._BIC_BCALL_NUM as Call,
PO02._BIC_BPIIN_23 as PIID,
VA.Program,
PO02.VENDOR as Vendor,
VA.VENDOR_NAME,
Q.QUARTER,
to_date(nullif(PO02._BIC_BCREATEDT,'00000000'),'YYYYMMDD') as Date,
LEFT(PO02._BIC_BORGDNUM,6) as DoDAAC,
count(PO02._BIC_BPO_ITMNO )as Lines,
sum(PO02._BIC_BNETVLCOU) as Value
FROM "NONFI_SHARED"."DOD_VA_SHARED_CONTRACT_LIST" VA
LEFT JOIN "BPURHO02" PO02
ON VA.CONTRACT#_USE = PO02._BIC_BPIIN_13
LEFT JOIN "NONFI_SHARED"."_PAA1222_QUARTERS" Q on PO02._BIC_BCREATEDT = Q.CREATE_DATE
WHERE
PO02._BIC_BMOD_NUM =''
and PO02.PROFIT_CTR LIKE '%5021318'
and PO02._BIC_BCREATEDT > '20170930'
and Q.QUARTER = &QDate
GROUP BY
/* PO02._BIC_BCONTRACT, */
PO02._BIC_BPIIN_13,
PO02._BIC_BCALL_NUM,
PO02._BIC_BPIIN_23,
PO02._BIC_BCREATEDT,
LEFT(PO02._BIC_BORGDNUM,6),
PO02._BIC_BMOD_NUM,
PO02.VENDOR,
VA.Contract#,
VA.Program,
VA.VENDOR_NAME,
Q.QUARTER
);
disconnect from pvcon;
quit;
/*break*/
proc sql ;
connect to saphana as pvcon (&PVCONSTR.);
create table w_MODS as select * from connection to pvcon
(SELECT
/* Case when substring(PO02._BIC_BCONTRACT,7,1) <> '-' then LEFT(PO02._BIC_BCONTRACT,6) || '-' || substring (PO02._BIC_BCONTRACT,7,2) || '-' || RIGHT(PO02._BIC_BCONTRACT,4) */
/* else PO02._BIC_BCONTRACT end as Contract /*this code adds dashes to contracts that don't have dashes*/
PO02._BIC_BPIIN_13 as Contract,
PO02._BIC_BCALL_NUM as Call,
PO02._BIC_BPIIN_23 as PIID,
VA.Program,
PO02.VENDOR as Vendor,
VA.VENDOR_NAME,
Q.QUARTER,
to_date(nullif(PO02._BIC_BCREATEDT,'00000000'),'YYYYMMDD') as Date,
LEFT(PO02._BIC_BORGDNUM,6) as DoDAAC,
count(PO02._BIC_BPO_ITMNO )as Lines,
sum(PO02._BIC_BNETVLCOU) as Value
FROM "NONFI_SHARED"."DOD_VA_SHARED_CONTRACT_LIST" VA
LEFT JOIN "BPURHO02" PO02
ON VA.CONTRACT#_USE = PO02._BIC_BPIIN_13
LEFT JOIN "NONFI_SHARED"."_PAA1222_QUARTERS" Q on PO02._BIC_BCREATEDT = Q.CREATE_DATE
WHERE
PO02._BIC_BMOD_NUM <>''
and PO02.PROFIT_CTR LIKE '%5021318'
and PO02._BIC_BCREATEDT > '20170930'
and Q.QUARTER = &QDate
GROUP BY
/* PO02._BIC_BCONTRACT, */
PO02._BIC_BPIIN_13,
PO02._BIC_BCALL_NUM,
PO02._BIC_BPIIN_23,
PO02._BIC_BCREATEDT,
LEFT(PO02._BIC_BORGDNUM,6),
PO02._BIC_BMOD_NUM,
PO02.VENDOR,
VA.Contract#,
VA.Program,
VA.VENDOR_NAME,
Q.QUARTER
);
disconnect from pvcon;
quit;
/********************************************************/
/* format fields */
/********************************************************/
data SHARED_OBS_wo_MODS;
set wo_MODS;
format LINES comma15.;
format VALUE dollar15.2;
format DATE mmddyy10.;
run;
data SHARED_OBS_w_MODS;
set w_MODS;
format LINES comma15.;
format VALUE dollar15.2;
format DATE mmddyy10.;
run;
/********************************************************/
/* EXPORT DATA */
/********************************************************/
PROC EXPORT DATA=SHARED_OBS_wo_MODS
OUTFILE="&exptOutPath/&outfile."
DBMS=xlsx REPLACE;
SHEET="wo_MODS";
RUN;
PROC EXPORT DATA=SHARED_OBS_w_MODS
OUTFILE="&exptOutPath/&outfile."
DBMS=xlsx REPLACE;
SHEET="w_MODS";
RUN;
/********************************************************/
/* EMAIL DISTRO */
/********************************************************/
filename mymail email
to=(
"name1 <name1@email.org>"
)
CC=(
)
from=('DISTRO <DISTRO@email.org>')
subject="Capital_Equipment_VA_Shared Obligations"
attach=( "&exptOutPath/&outfile" CONTENT_TYPE="application/xlsx")
;
data _null_;
file mymail;
put 'See attached.';
put ' ';
put 'Have a great day!';
put ' ';
put 'Thank you,';
run;
