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

[JaneNYC]

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	SCORE	TIER	MEAN	MAX	MIN
Safe_Total	82%	C	84%	95%	68%
Safe_Outcomes	60%		41%	0%	0%
Freq_OSI	40%		100%	0%	61%
AWOL	80%		91%	77%	83%
Safe_Practice_Score	95%		86%	77%	0%
Safe_Process_Score	99%		69%	77%	0%
Timeliness_OSI	100%		100%	40%	0%
Timeliness_Certs	98%		100%	79%	0%
Perm_Total	84%	C	85%	92%	69%
Perm_Outcomes_Score	81%		56%	0%	0%
Perm_Discharges	100%		100%	78%	0%
Adoption_Final	100%		99%	28%	0%
Perm_Practice_Score	85%		77%	0%	0%
FASP	100%		100%	46%	0%
WellBeing_Total	85%	B	85%	92%	77%
WellBeing_Out_Score	80%		80%	0%	0%
Step_Ups	40%		100%	81%	0%
Lateral_Moves	80%		93%	77%	0%
SD_Incentive	95%		6%	0%	0%
WellBeing_Practice_Score	88%		75%	0%	0%
FPS_Scores	89%	B	80%	90%	71%
Recruitment	84%		99%	60%	0%
FPS_Practice_Score	77%		66%	0%	0%
Foster_Training	83%		100%	40%	0%

[Cynthia_sas]

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	SCORE	TIER	MEAN	MAX	MIN
Safe_Total	82%	C	84%	95%	68%
Safe_Outcomes	60%		41%	0%	0%
Freq_OSI	40%		100%	0%	61%
AWOL	80%		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

[JaneNYC]

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

[JaneNYC]

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;

[Cynthia_sas]

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

1    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;