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 😃
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;
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;
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.
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
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.