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

[sross002]

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;

[SASJedi]

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;

[sross002]

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;

[sross002]

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;

[SASJedi]

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