proc report : color by condition

[AlexeyS]

Hello,

I want to ask you 2 questions about the same topic:

1. Some explanation. Avg_s1 &  Avg_s4 is simple average.Avg_n_s1 is average + number of observation in parenthesis, this is a character variable, for example, 3.5(24). I want to color this variable based on numeric variables Avg_s1 &  Avg_s4. And i don't want to display in the report Avg_s1 &  Avg_s4.

In the following code, when i change display to noprint option for Avg_s1 &  Avg_s4 i don't get any color.

proc sort data=report1_stat; by stn; run;

proc report data=report1_stat;
column stn  AVG_S4=AVG_S4_ALIAS AVG_S1 AVG_S4 avg_n_s1;
define stn / order;
define AVG_S4_ALIAS / noprint;
define AVG_S1 / display;
define AVG_S4 / display;
define avg_n_s1 / display &smpl1_label;
compute avg_n_s1;
if (AVG_S1/AVG_S4_ALIAS-1)>0.2 and  AVG_S4_ALIAS~=. then do;
   call define('avg_n_s1', "style", "style=[background=lightred]");
end;
if (AVG_S4_ALIAS/AVG_S1-1)>0.2 and  AVG_S4_ALIAS~=. then do;
   call define('avg_n_s1', "style", "style=[background=lightgreen]");
end;
endcomp;
run;

2. Suppose i have the following data, that consist of last 12 monthes and avertage for whole year, of course the months change dynamically during period. My question is how can i color months average values by condition, for example, if average month is higher by 20% from whole year average. Because i don't know exactly variable names.   

type	AVG	MAR22	FEB22	JAN22	DEC21	NOV21	OCT21	SEP21	AUG21	JUL21	JUN21	MAY21	APR21
A	9.49	5.47	9.25	9.85	9.12	6.36	10	17	9.83	7.35	6.44	11.17	8.56

THANK YOU!!!

[ChrisNZ]

1. Give us some sample data to play with.

2. What code do you  write if you don't know the variable names?

[AlexeyS]

1. DATA for question 1

STN	AVG_S1	AVG_S2	AVG_S4	AVG_N_S1	AVG_N_S2
A	3.2	4.2	3	3.2(5)	4.2(10)
B	2.1	1	3.5	2.1(4)	1*(40)
C	3	3.1	2.2	3(15)	3.1(58)

Now the code is working, but when i change option of AVG_S1 & AVG_S2 from dispay to noprint, because i dont want to see in the final report these variable, the color is disappearing.

2.Every month i run code, and i create a report for one year ago, so each time the names of variables MONYY can be changed.

THANK YOU

[Cynthia_sas · Accepted solution]

Hi:

  The challenge is that you NEED for the usage to be display, even with NOPRINT for those variables because without DISPLAY, the numeric variables will be treated as ANALYSIS variables with a statistic of SUM, which would change how you need to code them, using the variable.statistic syntax. With a usage of DISPLAY, that is what allows you to just use the variable name in the COMPUTE block. See below:

  You don't need the DISPLAY for the aliased column because the rule for aliased columns is that you can use the alias name as a reference in the COMPUTE block.

Cynthia

[ChrisNZ]

You don't reply to either question.

1. Data is expected to be given as code so we can use it.

2. No additional information provided.

Each time users ask a question like yours, the answer is always the same: Your data should be vertical. A database is not Excel. Like this:

data HAVE;
TYPE='A';
DATE='01MAR2022'd;AMT= 5.47; output;
DATE='01FEB2022'd;AMT= 9.25; output;		
DATE='01JAN2022'd;AMT= 9.85; output; 	
DATE='01DEC2021'd;AMT= 9.12; output;		
DATE='01NOV2021'd;AMT= 6.36; output;		
DATE='01OCT2021'd;AMT=10.00; output;	
DATE='01SEP2021'd;AMT=17.00; output;	
DATE='01AUG2021'd;AMT= 9.83; output;		
DATE='01JUL2021'd;AMT= 7.35; output;		
DATE='01JUN2021'd;AMT= 6.44; output;		
DATE='01MAY2021'd;AMT=11.17; output;	
DATE='01APR2021'd;AMT= 8.56; output;
DATE='01MAR2021'd;AMT= 8.56; output;
DATE='01FEB2021'd;AMT= 8.56; output;
DATE='01JAN2021'd;AMT= 8.56; output;
format date monyy5.;
proc tabulate data=HAVE ;
  where DATE > '01MAR2021'd;
  class TYPE DATE;
  var AMT;
  table TYPE=' ',DATE=' '*AMT=' '*sum=' ';
run;   

	APR21	MAY21	JUN21	JUL21	AUG21	SEP21	OCT21	NOV21	DEC21	JAN22	FEB22	MAR22
A	8.56	11.17	6.44	7.35	9.83	17.00	10.00	6.36	9.12	9.85	9.25	5.47