Exporting datasets to specific columns in excel sheets

[Santhosh_CHN]

Hi All,

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

Thanks,

[Cynthia_sas]

Hi,

  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.

cynthia

[Tal]

hey Cynthia,

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?

[Cynthia_sas]

Hi:

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.

cynthia

[Santhosh_CHN]

Hi &

What I did here is that, I renamed the column names separately like added SRC as prefix to the source attributes and TGT to Target attributes. Then I merged them and exported them to a single file.

,

Thanks for your suggestions.

Regards,

Santhosh

[Tal]

yes,i did find a good article by Harry Droogendyk.

Thanks Cynthia