topic Re: Exporting datasets to specific columns in excel sheets in ODS and Base Reporting

Exporting datasets to specific columns in excel sheets

Santhosh_CHN — Tue, 12 Nov 2013 21:57:30 GMT

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,

Re: Exporting datasets to specific columns in excel sheets

Cynthia_sas — Tue, 12 Nov 2013 22:47:50 GMT

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

Re: Exporting datasets to specific columns in excel sheets

Tal — Wed, 13 Nov 2013 03:16:04 GMT

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?

Re: Exporting datasets to specific columns in excel sheets

Cynthia_sas — Wed, 13 Nov 2013 04:27:33 GMT

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

Re: Exporting datasets to specific columns in excel sheets

Santhosh_CHN — Wed, 13 Nov 2013 19:58:33 GMT

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

Re: Exporting datasets to specific columns in excel sheets

Tal — Thu, 14 Nov 2013 05:14:55 GMT

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

Thanks Cynthia