topic Re: How do I combine multiple columns into one for output into Excel? in ODS and Base Reporting

How do I combine multiple columns into one for output into Excel?

midnight_blue — Thu, 03 Nov 2016 13:58:30 GMT

Hello, using SAS 9.4, I am using a nested analysis from proc tabulate, output into a temp dataset (data were originally calculated with a nested sum function in proc tabulate; I then used a datastep to perform some other arithmetic functions), then using proc print to output the aritmetic calculations to Excel.

For my final output, I would like to have some of the nested analysis levels within one column. Since I have to do this for three different top level analyses for multiple institutions, I am using a macro for the three different top levels.

Let's say I have a column with a variable named group, and the value is Institution. Then, the data are nested within Institution. For example, the proc tabulate would calculate Institution > Sex > Age_Group.

In the resulting dataset from proc tabulate, the columns (variables) would have:

Group | Sex | Age_Group

----------------------------------

Institution | Male | Age 18-64

Institution | Female | Age 65-74

The values for Sex and Age_Group apply a character format to display as male, age 18-64, etc. These are defined in the datastep after the output dataset from proc tabulate.

What I would like to see is (in one column from the output:

Group |

-----------------------------------

Institution Male Age 18-64 |

What I am currently getting is an output like this:

Group |

-----------------------------------

Institution Sex Age_Group |

My code in the data step is currently set up like this:

Group = "Institution &lvl1. &lvl2.";

Where "Institution" does not change (it will always preceed with Institution) and &lvl1. &lvl2. are the macro variables for Sex and Age_Group (these change depending on the analysis (can change to things like race, ethnicity, etc. but the basic idea is the same).

Then I proc print the resulting data from the data step (along with other variabless) into excel using the ods tagsets.excelxp

So basically: proc tabulate > output data > data step manipulation > ods tagsets excelxp proc print after the dataset data step manipulation

Any and all help appreciated.

Thank you!

Re: How do I combine multiple columns into one for output into Excel?

Reeza — Thu, 03 Nov 2016 13:57:17 GMT

Can't see the difference between what you have and want right now.

Re: How do I combine multiple columns into one for output into Excel?

midnight_blue — Thu, 03 Nov 2016 13:58:58 GMT

My mistake, I edited the post to reflect the difference.

Re: How do I combine multiple columns into one for output into Excel?

RW9 — Thu, 03 Nov 2016 14:00:45 GMT

So what is the actual question? Post test data (form of a datastep), and required output. Maybe also show your code as I don't see the need for macro here?

Re: How do I combine multiple columns into one for output into Excel?

ballardw — Thu, 03 Nov 2016 16:48:35 GMT

Why Excel? If you are putting what are actually multiple data fields into a single cell it seem highly unlikely that any one needs the Excel tools to further manipulate the data.

If you want SAS to write to column then you will need to create a new variable to hold the combined value.

something like

data want;

set have;

length longvalue $ 35;

longvalue = catx(' ',group,sex,age);

run;

You may have to fiddle with exactly how you want to combine the values, whether Put(variable,format.) may be needed instead of just the variable and such.Note that if you display the result with proportional fonts you will spend a lot time trying to get things to line up and I'm not going to go there for Excel output.

Then have your proc print only display the desired variables.

Re: How do I combine multiple columns into one for output into Excel?

midnight_blue — Thu, 03 Nov 2016 14:49:31 GMT

Thank you, I will have to try this and report back.