11-12-2013 04:57 PM
I have two data sets having columns Name, EMPID. Say, one is source and the other is Target and having the same column names. I want these two files to get exported to excel file, at different columns in a same sheet. Say Target needs to be at column A and Source needs to be at column D.
I also want to include a header before these two outputs in excel sheet as Source and Target. How can we do this? Any Help would be appreciated
11-12-2013 05:47 PM
There are several different ways to export your data files to Excel proprietary format: PROC EXPORT and the LIBNAME engine for Excel. You would have to use named ranges to get the output into specific columns, but that means some setup ahead of time to make the named ranges. However, that would not allow you to get the SOURCE and TARGET "headings" that you want.
On the other hand, you could use ODS destinations to create output for Excel. Getting the output side by side requires that you just use a TITLE statement and one of the ODS destinations that supports creating PANELed output. One such destination is ODS MSOFFICE2K_X, as described here (Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset). The example shows 3 tables horizontally, followed by 2 tables, but the general idea is there. You will need to download and install the TAGSETS.MSOFFICE2K_X destination control file (the tagset template), as described here (32394 - Installing and Storing Updated Tagsets for ODS MARKUP). This still doesn't give you the exact control you want (to place something in a fixed starting column), but it does get the output side-by-side.
11-12-2013 10:16 PM
you mind giving me a short example how we can export these 2 datasets to one excel file in 2 different columns with libname engine?
11-12-2013 11:27 PM
I don't have any examples already written that use named ranges. But I know there are some examples posted on support.sas.com and you can search for them. I believe there were also a lot of papers written about SAS and Excel...a few by Andrea Zimmerman and Harry Droogendyk stick in my mind. The topic should be Google-able.
But as I indicated, unless you "pre-format" the Excel file to have the "headings" SOURCE and TARGET, you will not be able to write them with SAS.
11-13-2013 02:58 PM