How can I retain formatting to my data when I export it to Excel and email it out?

sross002 — Fri, 17 Nov 2023 18:19:41 GMT

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;

Re: How can I retain formatting to my data when I export it to Excel and email it out?

Tom — Fri, 17 Nov 2023 18:30:38 GMT

Please be more specific about what you are having an issue with. What type of "formatting" do you mean?

Do you want the Excel sheet to arrange the data on the page the same way that SAS would?

Do you want the Excel sheet to display numbers the same way as SAS displays numbers?

Are you talking about how Excel displays dates?

Something else?

Which variable(s) are not showing the way you want?

What do you get now in Excel?

What do you want Excel to do instead?

Re: How can I retain formatting to my data when I export it to Excel and email it out

sross002 — Fri, 17 Nov 2023 18:36:37 GMT

The Value field doesn’t retain the dollar format I have written.

Re: How can I retain formatting to my data when I export it to Excel and email it out

Tom — Fri, 17 Nov 2023 19:54:33 GMT

You will probably want to make the XLSX file using ODS EXCEL so that you can use the TAGATTR style option to pass Excel formatting instructions.

Re: How can I retain formatting to my data when I export it to Excel and email it out

sross002 — Fri, 17 Nov 2023 19:55:37 GMT

Do you have an example? ##- Please type your reply above this line. No
attachments. -##

Re: How can I retain formatting to my data when I export it to Excel and email it out

Tom — Fri, 17 Nov 2023 20:23:31 GMT

A simple search found this example:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Currency-Format-Without-Decimal-Fraction-When-Output-to-Excel-By/td-p/18055

topic Re: How can I retain formatting to my data when I export it to Excel and email it out? in New SAS User

How can I retain formatting to my data when I export it to Excel and email it out?

Re: How can I retain formatting to my data when I export it to Excel and email it out?

Re: How can I retain formatting to my data when I export it to Excel and email it out

Re: How can I retain formatting to my data when I export it to Excel and email it out

Re: How can I retain formatting to my data when I export it to Excel and email it out

Re: How can I retain formatting to my data when I export it to Excel and email it out