Proc report / condensed 3-way output

[Cruise]

The desirable output from proc report is shown below. However my proc report program outputs expanded table which is not efficient. Also shown in image here. How should I adjust my proc report code for the desired layout? I apologize that I couldn't attach the subset of my dataset. I found only html path but not a file attached itself.

However, my current program gives me below, which is not space efficient.

My current program is:

proc import datafile="X:\Maggie\BMI_SHIFT_PCD_2017\DRAFT\sassupport.csv"
out=sassupport
dbms=csv replace;
run;

proc report data=sassupport nofs headline headskip;
title "Prevalence";
column ageyears survey_yr,(denom pr_obs) birth_yr;
define ageyears/group format=ageyears. 'Age Groups' order=internal;
define survey_yr/across width=4 'Survey Year';
define denom/display sum format=8.0 'N';
define pr_obs/analysis mean format=8.1 '%';
define birth_yr/group 'Cohort Year';
title 'PREVALENCE';
run; 

[Cynthia_sas · Accepted solution]

Hi:
  Are you sure your PROC REPORT is producing the above report. I only see a column for % I do not see one for N or DENOM-- nor do you have a compute block that's doing anything with DENOM.
 
For a clue about the solution, see the output on page 6 of this paper, https://www.sas.com/content/dam/SAS/support/en/technical-papers/SAS388-2014.pdf compared to the output on page 7, which shows the "stairstep" type of output.

  But another problem is your Cohort Year or BIRTH_YR variable on the right side of the report. PROC REPORT wants to make a row for every unique value of BIRTH_YR You have a format for AGEYEARS (but didn't provide it), but you don't have a format that will cause BIRTH_YR to collapse, so you're getting a row for each unique value of BIRTH_YR.

  You have some other issues with the code. HEADLINE and HEADSKIP are ignored for ODS so if you hope to do color coding such as you show, you'll need to tell us what your destination of choice is (HTML, RTF or PDF). Also, you have 2 usages for DENOM -- DISPLAY and SUM -- since SUM is listed second. It will be used. You probably meant to have ANALYSIS SUM there. But still the code you showed doesn't seem to have produced the screen shot you showed.
 
cynthia

[Cruise]

the paper you referred to is really good, solved the problems for me!!! thank you.