topic Re: Combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya in New SAS User

Combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya

sross002 — Fri, 17 Nov 2023 14:44:19 GMT

How can I combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya? I included the code below. As you will see, I am trying to put SHARED_OBS_wo_MODS in one Excel tab and SHARED_OBS_w_MODS into another Excel tab on the same Excel file. Any help is greatly appreciated, Thanks in advance 😃

/*UPDATE FY AND QTR*/

%let QDate = 'FY23 QTR4';

/*UPDATE FY AND QTR*/

%let exptOutPath = %str(~/OUTPUT);

%let outfile=%str(Capital_Equipment_VA_Shared Obligations.xlsx);

proc sql ;

connect to saphana as pvcon (&PVCONSTR.);

create table SHARED_OBS_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 SHARED_OBS_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;

Re: Combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya

SASJedi — Fri, 17 Nov 2023 14:57:53 GMT

One approach is to use the XLSX LIBNAME to create a new Excel workbook and write the results of each query to the XL library. They will surface as tabs in the Excel workbook. The code would look something like this:

libname xl xlsx "\\path to the Excel file folder\myWorkbook.xlsx";
proc sql ;
connect to saphana as pvcon (&PVCONSTR.);
create table XL.SHARED_OBS_wo_MODS as select * from connection to pvcon
... more code ...
quit;

proc sql ;
connect to saphana as pvcon (&PVCONSTR.);
create table XL.SHARED_OBS_w_MODS as select * from connection to pvcon
... more code ...
quit;

libname xl clear;

Re: Combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya

sross002 — Fri, 17 Nov 2023 15:20:21 GMT

thank you for your reply.
I was able to figure something out, but your reply is also helpful.
here is what I came away with:

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

Re: Combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya

sross002 — Fri, 17 Nov 2023 15:38:37 GMT

Sadly, I lose the formatting of the two datasets when I email it as one Excel file....

How can I retain the formatting in the final product that gets emailed? my code is below.... Any help is greatly appreciated. Thank you in advance.

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

/* 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=(

"Person1 <Person1@xxx.org>"

)

CC=(

)

from=('Email address <Email address@xxx.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,';

put ' ';

run;

Re: Combine two proc sql pass-through tables into one exported Excel file with two tabs in SAS Viya

SASJedi — Fri, 17 Nov 2023 16:17:02 GMT

Your solution will definitely work. If efficiency is important, this requires writing the output data twice - once to a SAS data set, and again, using PROC EXPORT, to the Excel file. The extra I/O will make this solution less efficient than using the XLSX LIBNAME engine. But if efficiency is not an issue - don't "fix" what isn't broken 😁.

All the best,

Mark