proc report-How to write shorter code

[Ronein]

Hello

I have some questions regrading this proc report code that I wrote:

1- Since there are 12 columns with names mon_1701 -mon_1712 (YYMM meaning).

What is a way to define these columns in more comfortable way than typing all of them  in 

Column statement ?

2- Is there a more clever way to prodcue multiple Define statements for each of the columns 

 mon_1701 -mon_1712?

I feel like stupid when I need to type them manually

3-Is there a clever way to color each cell that exceeds UCL  in a more elever way and not write the condition manually for each column?

 Data Wide1;
 input Topic UCL1 UCL2 mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
 mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712;
 cards;
 1 10 15 7 8 9 10 11 12 13 14 15 16 17 18  
 2 18 20 17 18 19 12 18 31 41 51 24 45 19 15 
 3 31 35 2 4 6 60 70 5 7 8 9 40 11 18 
 ;
 Run;




title;
proc report data=Wide1 nowd;
column 
Topic
UCL1
UCL2
mon_1701
mon_1702
mon_1703
mon_1704
mon_1705
mon_1706
mon_1707
mon_1708
mon_1709
mon_1710
mon_1711
mon_1712
;
define Topic / display  'Topic Nr';
define UCL1 / display  'Upper Limit1';
define UCL2 / display 'Upper Limit2';
define mon_1701/ display  "1701";
define mon_1702/ display  "1702";
define mon_1703/ display  "1703";
define mon_1704/ display  "1704";
define mon_1705/ display  "1705";
define mon_1706/ display  "1706";
define mon_1707/ display  "1707";
define mon_1708/ display  "1708";
define mon_1709/ display  "1709";
define mon_1710/ display  "1710";
define mon_1711/ display  "1711";
define mon_1712/ display  "1712";

compute mon_1701;
if mon_1701>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1701<=UCL2 and mon_1701>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute mon_1702;
if mon_1702>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1702<=UCL2 and mon_1702>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute mon_1703;
if mon_1703>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1703<=UCL2 and mon_1703>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute mon_1704;
if mon_1704>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1704<=UCL2 and mon_1704>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute mon_1705;
if mon_1705>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1705<=UCL2 and mon_1705>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;


compute mon_1706;
if mon_1706>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1706<=UCL2 and mon_1706>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;


compute mon_1707;
if mon_1707>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1707<=UCL2 and mon_1707>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;


compute mon_1708;
if mon_1708>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1708<=UCL2 and mon_1708>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;


compute mon_1709;
if mon_1709>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1709<=UCL2 and mon_1709>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute mon_1710;
if mon_1710>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1710<=UCL2 and mon_1710>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

compute mon_1711;
if mon_1711>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1711<=UCL2 and mon_1711>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;


compute mon_1712;
if mon_1712>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1712<=UCL2 and mon_1712>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;
Run;

[Kurt_Bremser]

By insisting to use the stupid wide dataset layout, you make your life unnecessary hard.

Data stupid;
input Topic UCL1 UCL2 mon_1701 mon_1702 mon_1703 mon_1704 mon_1705 mon_1706
 mon_1707 mon_1708 mon_1709 mon_1710 mon_1711 mon_1712;
 cards;
 1 10 15 7 8 9 10 11 12 13 14 15 16 17 18  
 2 18 20 17 18 19 12 18 31 41 51 24 45 19 15 
 3 31 35 2 4 6 60 70 5 7 8 9 40 11 18 
 ;
 
proc transpose data=stupid out=intelligent;
by topic ucl1 ucl2;
var mon:;
run;

data intelligent2;
set intelligent;
period = scan(_name_,2,'_');
run;

proc report data=intelligent2;
column topic ucl1 ucl2 period,col1;
define topic /group 'Topic Nr';
define ucl1 /group 'Upper Limit1';
define ucl2 /group 'Upper Limit2';
define period /across "";
define col1 /analysis sum "";
run;

[Ronein]

Thank you for your reply.

Let's say that I receive the report from another department and it is wide. 

Why? Because it is wide and there is no answer why.

From this point I need to deal with this report as it is.

I understand how to transform from wide to long via proc transpose.

My main question was how to create rule that each cell will be colored if its value exceeds UCL.

For example:

compute mon_1712;
if mon_1712>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1712<=UCL2 and mon_1712>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

[ballardw]

---

@Ronein wrote:

Thank you for your reply.

Let's say that I receive the report from another department and it is wide. 

Why? Because it is wide and there is no answer why.

From this point I need to deal with this report as it is.

I understand how to transform from wide to long via proc transpose.

My main question was how to create rule that each cell will be colored if its value exceeds UCL.

For example:

compute mon_1712;
if mon_1712>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1712<=UCL2 and mon_1712>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

---

Any thing you do with the provided poorly structured data is your work. You do not need to keep it in a bad form just because someone else does.

I rearrange data all the time because it makes for more reliable, consistent, relatively easy to follow and maintainable code.

Remember that Proc report builds things from left to right. So a column cannot reference another column further to the right for highlighting.

I'm tempted to bring up the "if everyone else is jumping off a cliff ..." analogy.

[Ronein]

It is great.

What is the way to colour the cells for each column  if it exceeds UCL?

I need to adjust this code in order that it will work with proc report when using across .

Need to apply this rule for all periods columns...

compute mon_1712;
if mon_1712>UCL2  then call define(_col_,"style","style={background=lightRed}");
else if mon_1712<=UCL2 and mon_1712>UCL1  then call define(_col_,"style","style={background=pink}");
else call define(_col_,"style","style={background=lightgreen}");
endcomp;

[PaigeMiller · Accepted solution]

Here's a brief example. In your PROC REPORT with an ACROSS statement, the column for 1701 is actually referred to as _C4_ (why? because it is the 4th column). So you could use something like this ( a short example which you can modify if you want more colors)

compute col1;
    if _c4_>=ucl then call define(4,'style','style={background=green}');
endcompute;

But then you would need to do this for column 5 and then column 6 and so on.

And this is a perfect place for macro! (NOTE: I have developed working SAS code for one column before I develop a macro for all the other columns)

%macro computeblock2;
	%do i=4 %to 15;
		if _c&i._>=ucl then call define(&i,'style','style={background=green}');
	%end;
%mend;

and then in PROC REPORT

compute col1;
    %computeblock2
endcompute;

I note that if the data changes and now you have a different 12 months, you do NOT have to change anything in your code.

[PaigeMiller]

This was all discussed in your earlier thread

https://communities.sas.com/t5/SAS-Programming/Create-Dynamic-labels-process/m-p/670504#M201298

If you insist on creating a wide data set and then using it for further analysis, this is inefficient as it takes you a lot more time to write the program. On the other hand, if you create a long data set, as shown in that thread and as shown by @Kurt_Bremser in his reply in this thread, the coding is simple. With a long data set, you can use the ACROSS option in PROC REPORT, and there is no need to type out all twelve months (in your case) or all the levels in other cases. And if you need to run the report again, with different months, you don't have to change the code in a zillion places.

There's a reason we keep saying this, and there's a reason that Maxim 19 exists.