11-03-2016 09:52 AM - edited 11-03-2016 09:58 AM
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:
Institution Male Age 18-64 |
What I am currently getting is an output like this:
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.
11-03-2016 10:00 AM
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?
11-03-2016 10:45 AM - edited 11-03-2016 12:48 PM
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.
length longvalue $ 35;
longvalue = catx(' ',group,sex,age);
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.