The SAS Output Delivery System and reporting techniques

Proc Report:formatting rows, specifying order, calculating a new variable...

Reply
Occasional Contributor
Posts: 9

Proc Report:formatting rows, specifying order, calculating a new variable...

Hi—

I am very new to proc report and I have an ok understanding of the basics but I have to do more with my report program and I’m having trouble digesting a lot of the online documentation. Any help is greatly appreciated.

The program below is what I have so far but there are several things I need to add.

  1. In there “Measure” Column there are certain rows that I need to indent and change the font weight and size for.
  2. The values in measure have to go in a certain order I’m having trouble with that. I thought specifying using “In” would keep the order but It is not.
  3. I need to get rid of the grid-lines and spread the report out more.
  4. I need to calculate a variable called TIER which awards letter grades based on the rage of scores for  'Safe_Total', 'Perm_Total' 'WellBeing_Total' 'FPS_Scores'.

I cut and pasted I sample of what I’m trying to accomplish. Again, I’m very new so  any help is greatly appreciated.

ods listing;

options leftmargin=".1 in" topmargin=".1 in"

        rightmargin=".1 in" bottommargin=".1 in"

        nodate nonumber orientation=landscape;

ods pdf file="Z:\SAS\SASCODE\Scorecardtest.pdf"

startpage=yes;

title 'FY12 Scorecard'  ;

footnote;

proc report data=have nowd

style(column)={just=c font_size=10pt font_face= 'Verdana'}

style(header)={font_size=10 pt font_face= 'Verdana' FONT_WEIGHT = BOLD };

column Measure SCORE1 mean1 Max1 min1;

define measure / display

style(column)={just=left font_size=10pt font_face= 'Verdana' cellwidth=4.in}

style(header)={font_size=10 pt font_face= 'Verdana' FONT_WEIGHT =BOLD};

where Measure in(

'Safe_Total'

'Safe_Outcomes'

'Freq_OSI'

'AWOL'

'Safe_Practice_Score'

'Safe_Process_Score'

'Timeliness_OSI'

'Timeliness_Certs'

'Perm_Total' 

'Perm_Outcomes_Score' 

'Perm_Discharges' 

'Adoption_Final'

'Perm_Practice_Score'

'FASP'

'WellBeing_Total'

'WellBeing_Out_Score'

'Step_Ups'

'Lateral_Moves'

'SD_Incentive'

'WellBeing_Practice_Score'

'FPS_Scores'

'FPS_Practice_Score'

'Recruitment'

'Foster_Training'

); run;

title;

footnote;

ods _all_ close;

FY12 SCORECARD
Measure SCORETIERMEANMAXMIN
Safe_Total82%C84%95%68%
Safe_Outcomes60%41%0%0%
     Freq_OSI40%100%0%61%
     AWOL80%91%77%83%
Safe_Practice_Score95%86%77%0%
Safe_Process_Score99%69%77%0%
    Timeliness_OSI100%100%40%0%
    Timeliness_Certs98%100%79%0%
Perm_Total 84%C85%92%69%
Perm_Outcomes_Score81%56%0%0%
    Perm_Discharges100%100%78%0%
    Adoption_Final100%99%28%0%
Perm_Practice_Score85%77%0%0%
    FASP100%100%46%0%
WellBeing_Total85%B85%92%77%
WellBeing_Out_Score80%80%0%0%
    Step_Ups40%100%81%0%
    Lateral_Moves80%93%77%0%
    SD_Incentive95%6%0%0%
WellBeing_Practice_Score88%75%0%0%
FPS_Scores89%B80%90%71%
    Recruitment84%99%60%0%
    FPS_Practice_Score77%66%0%0%
Foster_Training83%100%40%0%
SAS Super FREQ
Posts: 8,645

Re: Proc Report:formatting rows, specifying order, calculating a new variable...

Hi:

  Some initial thoughts:

  1. In there “Measure” Column there are certain rows that I need to indent and change the font weight and size for.

--Use STYLE= overrides for this ..you will probably have to use a COMPUTE block, but you might be able to use a FORMAT -- the style attribute you want to change will either be INDENT= or LEFTMARGIN= for the MEASURE column -- one of them works better with RTF and the other with PDF.

2.      The values in measure have to go in a certain order I’m having trouble with that. I thought specifying using “In” would keep the order but It is not.

--I believe you will need to create a "fake" ordering variable and have it appear before your Measure column. But since REPORT allows you to use NOPRINT --then you can use it for ordering, but not display it on the report

3.     I need to get rid of the grid-lines and spread the report out more.

--Try using STYLE=JOURNAL as a place to start. you can specify OUTPUTWIDTH=6in or WIDTH=6in to spread it out more

4.     I need to calculate a variable called TIER which awards letter grades based on the rage of scores for  'Safe_Total', 'Perm_Total' 'WellBeing_Total' 'FPS_Scores'.

--Use a COMPUTE block for this, but I'm not sure what you mean by "range of scores" Let's look at the first section:

Measure SCORETIERMEANMAXMIN
Safe_Total82%C84%95%68%
Safe_Outcomes60%41%0%0%
     Freq_OSI40%100%0%61%
     AWOL80%91%77%83%

Are you ONLY testing on the 82% to get the C or are you basing the score on MEAN MAX and MIN for Safe_Total. This will make a difference to PROC REPORT because it has a "left to right" order for how it places items on the report row and it can't calculate TIER before it places MEAN, MIN and MAX on the report row.

Take a look at this paper and how the INDENT and row ordering happen for example 1. That should get you started.

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

cynthia

Occasional Contributor
Posts: 9

Re: Proc Report:formatting rows, specifying order, calculating a new variable...

This is great Cynthia! Thanks so much. I may have to bug you with a few more questions. Thanks!!

Occasional Contributor
Posts: 9

Re: Proc Report:formatting rows, specifying order, calculating a new variable...

Hi Cynthia--

So, I have been working on the program above and I'm still have issues. Any advice would be greatly appreciated.

I made a sorting variable like you suggested  and then I was trying to use the sorting variable as a way to modify the indentation and font weight in certain cells in the column measure “Measure?” . Again, I'm trying to produce this:


Safe_Total
Safe_Outcomes
     Freq_OSI
     AWOL
Safe_Practice_Score
Safe_Process_Score
    Timeliness_OSI
    Timeliness_Certs
Perm_Total
Perm_Outcomes_Score
    Perm_Discharges
    Adoption_Final
Perm_Practice_Score
    FASP
WellBeing_Total
WellBeing_Out_Score
    Step_Ups
    Lateral_Moves
    SD_Incentive
WellBeing_Practice_Score
FPS_Scores
    Recruitment
    FPS_Practice_Score
Foster_Training


Here I make the sort variable:


data Scorecared_vars ;

retain sort Agency_code agency Measure score; 

set Scorecared_vars;

if Measure='Safe_Total' then sort=1;

if Measure='Safe_Outcomes' then sort=2;

if Measure= 'Freq_OSI' then sort= 3   ;

if Measure= 'AWOL' then sort=4  ;

if Measure= 'Safe_Practice_Score' then sort= 5;

if Measure= 'Safe_Process_Score' then sort=6;

if Measure= 'Timeliness_OSI'  then sort= 7  ;

if Measure= 'Timeliness_Certs' then sort= 8  ;

if Measure= 'Perm_Total' then sort= 9 ;

if Measure= 'Perm_Outcomes_Score' then sort=  10 ;

if Measure= 'Perm_Discharges' then sort= 11  ;

if Measure= 'Adoption_Final'  then sort= 12  ;

if Measure= 'Perm_Practice_Score' then sort= 13  ;

if Measure= 'FASP'    then sort= 14  ;

if Measure= 'WellBeing_Total' then sort= 15  ;

if Measure= 'WellBeing_Out_Score' then sort= 16  ;

if Measure= 'Step_Ups'    then sort=17   ;

if Measure= 'Lateral_Moves' then sort=18   ;

if Measure= 'SD_Incentive' then sort=19   ;

if Measure= 'WellBeing_Practice_Score' then sort=20   ;

if Measure= 'FPS_Scores' then sort= 21  ;

if Measure= 'FPS_Practice_Score' then sort=22   ;

if Measure=  'Recruitment' then sort=23   ;

if Measure=  'Foster_Training'  then sort= 24  ;

run;

proc sort data=Scorecared_vars_withms; 

by  descending Agency_code  sort ; run;

Here is the rest of the program:

ods listing;

options leftmargin=".1 in" topmargin=".1 in"

       rightmargin=".1 in" bottommargin=".1 in"

      nodate nonumber orientation=landscape;

ods pdf file="Z:\SAS\SASCODE\Scorecardtest.pdf"

style=journal

startpage=yes;

ods escapechar='^';

title j=l '^S={preimage="Z:\SAS\SASCODE\NYC ACS Logo2.jpg"}';

title2 'FY12 Familly Based Scorecard'  ;

footnote;

proc report data=Scorecared_vars_withms nowd

style(column)={just=c font_size=10pt font_face= 'Verdana'}

style(header)={font_size=10 pt font_face= 'Verdana' FONT_WEIGHT = BOLD background=cxcccccc};

column Measure SCORE1 mean1 Max1 min1;

define measure / display

style(column)={just=left font_size=10pt font_face= 'Verdana' cellwidth=4.in}

style(header)={font_size=10 pt font_face= 'Verdana' FONT_WEIGHT =BOLD};

compute Measure;

  If sort=1 then ;

    CALL DEFINE('_c1_',"STYLE","STYLE={PRETEXT='   ' ASIS=ON FONT_STYLE=BOLD}");

endcomp;

run;

title;

footnote;

ods _all_ close;

SAS Super FREQ
Posts: 8,645

Re: Proc Report:formatting rows, specifying order, calculating a new variable...

Hi:

  It seems to me that you are trying to indent with PRETEXT=. I'm not sure why you're doing that. In my example, the indenting happened with LEFTMARGIN= style attribute. Next, it seems to me that you are creating SORT variable, but then you're not USING the SORT variable in your PROC REPORT step. You can only test in a COMPUTE block what is in the COLUMN statement. WHERE do you list SORT in your COLUMN statement?

  And, it looks like you are assigning sequential numbers to each category. Based on what you originally showed, you want SORT to be

Sort Measure

  Safe_Total

2    Safe_Outcomes

3         Freq_OSI

4         AWOL

5    Safe_Practice_Score

So you are going to control ORDER using the SORT variable, but that means if you want to TEST on SORT, it has to be in the COLUMN statement. AND, it seems to me that you want to indent if SORT=3 or SORT=4.....not if SORT=1. It looks to me like 1, 2 and 5 are NOT indented.

Also, you do NOT need '_c1_' in your CALL DEFINE statement. I don't see an ACROSS usage in your code. A simple _COL_ will work here. See the example below. I just made some fake data without all your variables or rows. But I did put SORT in the COLUMN statement.

cynthia

data janerept;

  length measure $20;

  infile datalines dlm=',';

  input sort measure $ value;

return;

datalines;

1, Safe_Total, 10

2, Safe_Outcomes, 20

3, Freq_OSI, 21

4, AWOL, 22

5, Safe_Practice_Score, 30

;

run;

 

title;

ods listing close;

ods pdf file='c:\temp\janerept.pdf'

    style=journal notoc;

  

proc report data=janerept nowd;

  column sort measure value;

  define sort / order noprint;

  define measure / display 'Measure';

  define value / sum 'Value';

  compute measure;

    if sort in (2, 3) then

       call define(_col_,'style','style={leftmargin=12pt}');

    else if sort = 1 then

       call define(_col_,'style','style={font_weight=bold}');

  endcomp;

run;

ods pdf close;

Post a Question
Discussion Stats
  • 4 replies
  • 292 views
  • 1 like
  • 2 in conversation