BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
midnight_blue
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

5 REPLIES 5
Reeza
Super User

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

midnight_blue
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 10367 views
  • 0 likes
  • 4 in conversation