topic Merge two or more SAS datasets into Excel in Microsoft Integration with SAS

Merge two or more SAS datasets into Excel

Wayne_PSU — Fri, 05 Aug 2011 17:30:20 GMT

Can I "merge" two or more SAS datasets into Excel? If so, how?

Excel 2010

SAS Add-in 4.3

Thank you.

Merge two or more SAS datasets into Excel

art297 — Fri, 05 Aug 2011 17:35:42 GMT

I think you have to better explain what you are trying to do. You can always merge two or more datasets using either a datastep or proc sql and then export the file to Excel.

Merge two or more SAS datasets into Excel

Wayne_PSU — Fri, 05 Aug 2011 18:05:55 GMT

Here's some additional detail:

I've just set up SAS-EBI 9.3 and installed MS Add-In for Excel 4.3 on a client to test. I am exploring the functionality of the SAS tab within Excel and would like to create a filter that accesses variables from more than one SAS dataset in a SAS library.

Thank you.

Merge two or more SAS datasets into Excel

Cynthia_sas — Sun, 07 Aug 2011 16:36:56 GMT

Hi:

This sounds like a job for either an information map or a stored process.

In the information map, you can define the relationship between the tables (one to many, one to one, etc) and the type of join. Then, the information map can be accessed by any of the client applications that support information map access. The end user will only see the "final" results and will not have to worry about the fact that what they see is the result of a join/merge process.

Another alternative is to code you join in a stored process and return the stored process results to Excel using the SAS Add-in. The method you use will depend on what you feel comfortable with and whether you are comfortable with Information Map Studio and/or the coding needed to create a stored process.

cynthia

Merge two or more SAS datasets into Excel

Wayne_PSU — Mon, 08 Aug 2011 19:41:35 GMT

Thank you Cynthia.

I appreciate that there are various ways to create data for use in Excel. However, in this situation I would like end users to be able to access various existing datasets via the SAS Add-in for Excel, then create a filter to refine the results.

Merge two or more SAS datasets into Excel

LinusH — Wed, 10 Aug 2011 08:35:16 GMT

The two most common ways for end users to combine tables in SAS is (as Cynthia mentioned) is Information Maps (to have a power user define them for you, or if you are competent enough, do it your self), and Enterprise Guide. There is no currently available functionality in AMO to combine tables

If your users are competent enough to combine tables, I think that Enterprise Guide could be suitable tool for them.

/Linus