Help using Base SAS procedures

Proc report / condensed 3-way output

Accepted Solution Solved
Reply
Super Contributor
Posts: 354
Accepted Solution

Proc report / condensed 3-way output

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.

 

example.png

 

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

 

ex.png

 

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; 

 


Accepted Solutions
Solution
‎01-05-2018 10:46 AM
SAS Super FREQ
Posts: 9,364

Re: Proc report / condensed 3-way output

[ Edited ]

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

View solution in original post


All Replies
Solution
‎01-05-2018 10:46 AM
SAS Super FREQ
Posts: 9,364

Re: Proc report / condensed 3-way output

[ Edited ]

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

Super Contributor
Posts: 354

Re: Proc report / condensed 3-way output

Posted in reply to Cynthia_sas
the paper you referred to is really good, solved the problems for me!!! thank you.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 255 views
  • 1 like
  • 2 in conversation