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.
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% |
Hi:
Some initial thoughts:
--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
This is great Cynthia! Thanks so much. I may have to bug you with a few more questions. Thanks!!
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.