topic Re: SAS add in for Excel -- splitting age into three categories in SAS Enterprise Guide

SAS add in for Excel -- splitting age into three categories

ABZ — Mon, 22 May 2017 13:33:52 GMT

Hi, I am new to SAS add in for Excel, and I'm trying to create age categories from a numeric age variable (i.e., take varaible age in years and break it into ages 16-30,31-50,51-64. I've tried using the split columns task, but no luck. Suggestions?

Re: SAS add in for Excel -- splitting age into three categories

Kurt_Bremser — Mon, 22 May 2017 13:50:39 GMT

The SAS Add-In for Microsoft office can only deliver data from SAS; either from datasets, multidimensional cubes, or stored processes. It does not offer a program interface, for this you either need SAS Studio or Enterprise Guide.

So you either do your logic in Excel, or you switch to programming the logic in SAS, using one of the aforementioned tools. From there you can either use the Add-In to retrieve the manipulated data (store the resulting dataset in a permanent library), or you can export to an Excel workbook.

Re: SAS add in for Excel -- splitting age into three categories

ABZ — Mon, 22 May 2017 13:59:32 GMT

Thanks! So I basically have to create the new variables in excel first? Do you know what the “Split Columns” task in SAS add-in is used for?

Re: SAS add in for Excel -- splitting age into three categories

ChrisHemedinger — Mon, 22 May 2017 13:59:58 GMT

Actually, the SAS Add-In for Office does offer a program editor interface now! You can create and run SAS programs, if you want.

However, the approach to your problem is probably best served by using SAS formats, which allow you to define age ranges into categories, then create a report based on those categories.

The Create Format task is not part of the SAS Add-In menu (I don't think) -- so you could do that step in code or via SAS Enterprise Guide, then store the format definition in a library that you can access in your SAS Add-In for Microsoft Office session. Then the Summary Tables report or even One-Way Frequencies could be used.

Or you can add these classifications to Excel -- then the range rules would be part of your spreadsheet, not part of your SAS process. That's up to you.

Re: SAS add in for Excel -- splitting age into three categories

Kurt_Bremser — Mon, 22 May 2017 14:10:23 GMT

@ChrisHemedinger wrote:

Actually, the SAS Add-In for Office does offer a program editor interface now! You can create and run SAS programs, if you want.

Heh. Have to test that out, once I'm through with the upgrade to SAS 9.4 😉

Althought my preferred method will always be to keep the logic in SAS codes that can be reviewed & versioned. Makes for a simpler life with auditors.

Re: SAS add in for Excel -- splitting age into three categories

ChrisHemedinger — Mon, 22 May 2017 14:28:54 GMT

You can see this feature in action - here's a demo from the SAS R&D developer!