BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AlexeyS
Pyrite | Level 9

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.   

typeAVGMAR22FEB22JAN22DEC21NOV21OCT21SEP21AUG21JUL21JUN21MAY21APR21
A9.495.479.25

9.85

9.126.3610179.837.356.4411.178.56

 

THANK YOU!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1651016885667.png

  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

View solution in original post

4 REPLIES 4
ChrisNZ
Tourmaline | Level 20

1. Give us some sample data to play with.

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

AlexeyS
Pyrite | Level 9

1. DATA for question 1

STNAVG_S1AVG_S2AVG_S4AVG_N_S1AVG_N_S2
A3.24.233.2(5)4.2(10)
B2.113.52.1(4)1*(40)
C33.12.23(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
SAS Super FREQ

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:

Cynthia_sas_0-1651016885667.png

  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
Tourmaline | Level 20

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

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 847 views
  • 0 likes
  • 3 in conversation