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

[midnight_blue]

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!

[Reeza]

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

[midnight_blue]

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

[RW9]

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?

[ballardw · Accepted solution]

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.

[midnight_blue]

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