Proc report- color coding the results with difference in total

[Stalk]

Have..

State	BiWeekly_target	wk	Reached_status
AK	15	2021-02-14 To 2021-02-20	12
AL	11
AR	8
AZ	12	2021-02-07 To 2021-02-13	8
CO	10	2021-02-14 To 2021-02-20	9
CT	11	2021-02-14 To 2021-02-20	11
DC	16	2021-02-07 To 2021-02-13	12
DE	9	2021-02-14 To 2021-02-20	9

Want

		week
		2021-02-07 To 2021-02-13	2021-02-14 To 2021-02-20	Reached_status
state	BiWeekly_target				Diff	%Diff
AK	15	-	12	12	3
AZ	18	8	-	8	10
CO	10	-	9	9	1
CT	11	-	11	11	0
DC	16	12	-	12	4
DE	9	-	9	9	0

So far tried the following..
ODS excel options(sheet_name=' Counts ' autofilter='all' embedded_titles='yes' embedded_footnotes='yes'
absolute_column_width='10,45,15,25,25,15' flow = "header, data");

proc report data=target_stat nowd SPLIT='*' ;*completerows;
column state BiWeekly_target Category, Reached_status ('Total' Reached_status =tot);;

define state / group width= 45 " Submitter";
define BiWeekly_target / group width= 45 " BiWeekly * Target";
define Category/ across width= 30 order= Internal "week ";
define Reached_status / sum width= 30 " ";
define tot / sum ' ';
run;

Need to achieve..
1) Show all states irrespective of reached_status For ex: AL & AR- completerows is not working as desired
2) Add new column in report to show the difference BiWeekly_target- Reached_status
3) Add another column to show the percent difference.
4) Color, if difference is zero then target reached so it should be green, If diff greater than 75% then it should be yellow
otherwise red.
5) Is it possible to wrap the second row in the report( dates row)?

thank you

[ballardw]

---

@Stalk wrote:

Have..

State	BiWeekly_target	wk	Reached_status
AK	15	2021-02-14 To 2021-02-20	12
AL	11
AR	8
AZ	12	2021-02-07 To 2021-02-13	8
CO	10	2021-02-14 To 2021-02-20	9
CT	11	2021-02-14 To 2021-02-20	11
DC	16	2021-02-07 To 2021-02-13	12
DE	9	2021-02-14 To 2021-02-20	9

Want

		week
		2021-02-07 To 2021-02-13	2021-02-14 To 2021-02-20	Reached_status
state	BiWeekly_target				Diff	%Diff
AK	15	-	12	12	3
AZ	18	8	-	8	10
CO	10	-	9	9	1
CT	11	-	11	11	0
DC	16	12	-	12	4
DE	9	-	9	9	0

So far tried the following..
ODS excel options(sheet_name=' Counts ' autofilter='all' embedded_titles='yes' embedded_footnotes='yes'
absolute_column_width='10,45,15,25,25,15' flow = "header, data");

proc report data=target_stat nowd SPLIT='*' ;*completerows;
column state BiWeekly_target Category, Reached_status ('Total' Reached_status =tot);;

define state / group width= 45 " Submitter";
define BiWeekly_target / group width= 45 " BiWeekly * Target";
define Category/ across width= 30 order= Internal "week ";
define Reached_status / sum width= 30 " ";
define tot / sum ' ';
run;

Need to achieve..
1) Show all states irrespective of reached_status For ex: AL & AR- completerows is not working as desired
2) Add new column in report to show the difference BiWeekly_target- Reached_status
3) Add another column to show the percent difference.
4) Color, if difference is zero then target reached so it should be green, If diff greater than 75% then it should be yellow
otherwise red.
5) Is it possible to wrap the second row in the report( dates row)?

thank you

---

1) Your code shows *completerows; which would comment the option and not be used.

Your columns statement shows a variable name Category that you do not show as being in your "have" data.

Your output shows a column heading of "week" for the non-existant variable Category

So I don't think you are showing either the code or the data you are actually using.

Start over providing actual data, in the form of a data step that will allow us to recreate a partial data set that will run with the code. Double check your code and post a corrected version that matches the data.

[Stalk]

Category is nothing but the wk in my have data. Across made the columns as date fields and column header as week
define Category/ across width= 30 order= Internal "week ";

I commented the completerows because it's creating unnecessary empty rows.
Hope this resolves confusion

[Cynthia_sas]

Hi:
Without data in readable form, it is hard to guess. Without data, no one can run your code. It looks like your AL and AR rows do not have a value for Category or values for the reached status variable either. So in an across scenario, there's no category or week to put them under.

I don't think you need COMPLETEROWS here. And I'm not sure you need Bi-Weekly Target to be a group usage. if you have more than one row per state, per week, then don't you want all the bi_weekly_target values to be added up. That variable would need to be an analysis variable for that to happen. You only show one week for each state, but February has had more weeks than that, so at this point in time, shouldn't there be more rows for each state? This is why it is hard to guess at a possible solution.

I don't know whether you posted actual data or a PROC PRINT or PROC REPORT output report where you show "have". Also, for ODS EXCEL, options like WIDTH=30 and I don't see the TOT column that you've created as an alias, so I wonder whether any of what you posted was created from a working program.

Cynthia

[Stalk]

Cynthia, thank you for your feedback..
Here is the data and the code..
I want to show AL and AR on the report even thought they don't have any data.
My percentages are messed up. I would like to round to 100% it the total is greater than the biweekly target. and color code based on the diff or pct.
red for any thing below 70% and yellow 71% to 89% and green for 90 and above.
data have;

infile datalines;
input State $ BiWeekly_target wk : date. Reached_status ;

datalines;

AK 15 20FEB2021 12
AL 11
AR 8
AZ 12 13FEB2021 8
CO 10 20FEB2021 9
CT 11 13FEB2021 11
DC 16 20FEB2021 12
DE 9 20FEB2021 9
;
run;
proc report data=have nowd SPLIT='*' ;
column state BiWeekly_target wk,
Reached_status ('Total' Reached_status =tot) diff diff_pct;
define state / group width= 45 " State ";
define BiWeekly_target / group width= 15 " Bi-Weekly * Counts";
define wk/ across width= 30 order= Internal "week ";
define Reached_status / sum width= 30 " ";
define tot / sum ' ';
define diff / computed " shortage";
compute diff;
diff=BiWeekly_target -tot;
endcomp;
define diff_pct / computed " % shortage" format=percent6. width=8;
compute diff_pct;
diff_pct=(tot/BiWeekly_target)*100;
endcomp;
rbreak after / summarize;
run;

[Cynthia_sas · Accepted solution]

Hi:

  I don't understand what you mean about rounding to 100% if the total is greater than the BiWeekly_Target. I don't understand why your calculation multiplies by 100 because the percent format that you've using also multiplies by 100, so I just changed your formula a bit. In order to use BiWeekly_Target in any calculation, you must use the compound name BiWeekly_Target.sum because BiWeekly_Target is an analysis variable with a usage of SUM. I also wasn't clear on the percents. The issue with your data, as you sent it is that you have missing values for WK for both AL and AR, so they don't have a place to fit under. Also your REACHED_STATUS for thos 2 rows is going to be missing, which will mess up the DIFF calculation and the percent calculation. I did the color coding that made sense to me, you might need to tweak it.

  Here's the slightly changed data and code I used:

Note that I moved a lot of the column headers up into the column statement itself in order to simplify the headers for the ACROSS variables.

  And the output -- I only did the red and green because I did not really understand the rounding your envision. Also, see how AL and AR have 0 under the missing column for WK -- if their week value is missing and their reached_status value is missing, then there is no difference that can be calculated for them:

Hope this helps,

Cynthia

[Stalk]

Thank you Cynthia for taking time to fix my code, especially the column header and percentages that are bothering me so much. I have one question, some how my autofilter='all' function is not working when I output to excel. Any thoughts?

[Cynthia_sas]

Hi:

  I rarely use autofilter and when I do, I NEVER use ALL as the option. Usually, I have numeric variables in my output and I only like having autofilter on my character variables because those are usually the values I want to filter on. However, when I use ODS EXCEL with the same PROC REPORT code as previous posted, I do get autofilter on all the columns:

But as I said, in this context, it doesn't make sense to me to have autofilter on anything other than the first column. The reason is that it doesn't make sense to work so hard at getting a particular structure to the output only to go and use autofilter on ALL the columns for this data. My inclination would be to just put autofilter like this:

  If you're having issues with autofilter, that's something you may want to work out with Tech Support. They have the ability to look at all your real data and your real code and take your version of SAS into account to help you come to a resolution.

Cynthia

[Stalk]

Thank you Cynthia. Autofilter for first column is working.
To answer your previous question: I don't understand what you mean about rounding to 100% if the total is greater than the BiWeekly_Target.
if the reached_status greater than BiWeekly target then the percentage i s exceeding 100% . Say for ex: AZ, If I change the reached_status to 15 instead of 8. Percent will be 125%. In this case I would like to show that as 100%.

[Cynthia_sas]

Hi:

  Then I would probably change the COMPUTE block to alter and then do the highlighting based on the values of DIFF_PCT, as shown below:

Remember that the format is doing the multiply by 100 so the values you have to test are the decimal values after the division but before the format is applied.

Cynthia