BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
NagendraBS
Fluorite | Level 6

Hi All,

I have a dataset with actual values and % values for each month as below 

SEG

AMOUNT

M1

M2

M3

M1%

M2%

M3%

ABC

100

   2,200

   2,420

   2,662

97%

98%

100%

ABC

200

   1,458

   1,604

   1,764

93%

95%

99%

ABC

300

   1,345

   1,480

   1,627

96%

100%

99%

ABC

400

      870

      957

   1,053

87%

97%

96%

ABC

500

      554

      609

      670

68%

92%

99%

BCD

100

   4,450

   4,895

   5,385

99%

98%

97%

BCD

200

   1,278

   1,406

   1,546

93%

96%

100%

BCD

300

   3,345

   3,680

   4,047

100%

88%

94%

BCD

400

   1,879

   2,067

   2,274

99%

97%

97%

BCD

500

      665

      732

      805

78%

91%

99%

 

I need to color code both the columns M1 & M1% cells based on M1% column values. Let say if M1%>95% then green (both M1 & M1% cells); else if m1% >90% then orange; else red. The same format should apply for all the subsequent columns  as below. 

NagendraBS_0-1707726516900.png

 

I did try using the PROC REPORT to achieve the result but unable to get color format for both actuals and percentage values at the same time in proc report. Below is snippet used to achieved the partial results. 

 

Query:

 proc report data=work.freq_accts;

     column cas level M1 M2 M1% M1% M2% M1%%; 

define cas / display 'CAS';

        define level / display 'Plan Amt';

        define M1 / display '1Month' ;

        define M2 / display '2Months' ;

        define M3% / display '3Months' ;

define M1% / display 'M1%' ;

        define M2% / display 'M2%' ;

        define M3% / display 'M3%' ;

        

compute M1%;

          if M1% > .95 then 

         call define ('M1', "style","style={background=light green}");

          else if M1% > .90 then

         call define ('M1', "style","style={background=light orange}");

      else if M1% ne . then

         call define ('M1', "style","style={background=red}");

        endcomp;

 

        compute M2%;

          if M2% > .95 then

         call define ('M2', "style","style={background=light green}");

          else if M2% > .90 then

         call define ('M2', "style","style={background=light orange}");

      else if M2% ne . then

         call define ('M2', "style","style={background=red}");

        endcomp;

 

        compute M3%;

          if M3% > .95 then

         call define ('M1', "style","style={background=light green}");

          else if M3% > .90 then

         call define ('M1', "style","style={background=light orange}");

      else if M3% ne . then

         call define ('M1', "style","style={background=red}");

        endcomp;

     

    run;

 

Based on the above query able to get the results as below 

 

NagendraBS_1-1707726516902.png

 

Now I wanted to color code the other columns as well based on the same condition. If not possible need to eliminate the % columns and keep the color format for actuals. 

 

Thanks in advance. 

 

Thanks,

Neel

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:

  I'm not sure that the code you posted produces the output you showed. As an example, your PROC REPORT step shows 

Cynthia_sas_0-1707748254545.png

You show a CAS variable and a LEVEL variable and an M1%, M2% and M3% in the DEFINE statements, but you show M1% 3 times in the COLUMN statement and so I would expect your code to generate errors and no color coding or error prone color coding at all. Your output shows SEG and AMOUNT variables, but those aren't the labels in the PROC  REPORT, so that is problematic.

 

  Are your percent variables (which you have labelled M1%, M2% M3%) character or numeric? Based on your IF statements, my suspicion is that they are numeric, but I also suspect your code has other issues. Are your variables really named M1%, M2% and M3%? I would expect those variable names to generate errors in the SAS Log. Also, your M1, M2 and M3 variables are definitely numeric, but you don't show any SAS format in the DEFINE statement that would format the numbers with a COMMA format. In addition, your IF statement treats M1% as numeric with a value of .95 and .90, etc, but again, there's no format being used in the PROC REPORT step to format the decimal number as a percent.

  Is it possible to see your SAS log that's generating the  results you posted?  I would be curious to see how your reference to M1%, M2%, etc is being handled. 

  There are a few different ways to handle having the percent columns with the same background color as the M1, M2 and M3 variables. One way is to use another CALL DEFINE in the COMPUTE block and an alternate way is to use traffic-lighting with a user-defined format for the percent variables and then a simplified COMPUTE block for the M1, M2 and M3 values.

Cynthia

 

  With some fake data and different variable names, I think either of these examples will produce the report you want. Note that the second example has a simplified COMPUTE block, but the first example also works, although it is more verbose. ( I only typed a few rows of data since I had to make adjustments for the < and the % in the example data you posted.)

Cynthia_sas_0-1707753542259.png

 

 

 

View solution in original post

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

Hi:

  I'm not sure that the code you posted produces the output you showed. As an example, your PROC REPORT step shows 

Cynthia_sas_0-1707748254545.png

You show a CAS variable and a LEVEL variable and an M1%, M2% and M3% in the DEFINE statements, but you show M1% 3 times in the COLUMN statement and so I would expect your code to generate errors and no color coding or error prone color coding at all. Your output shows SEG and AMOUNT variables, but those aren't the labels in the PROC  REPORT, so that is problematic.

 

  Are your percent variables (which you have labelled M1%, M2% M3%) character or numeric? Based on your IF statements, my suspicion is that they are numeric, but I also suspect your code has other issues. Are your variables really named M1%, M2% and M3%? I would expect those variable names to generate errors in the SAS Log. Also, your M1, M2 and M3 variables are definitely numeric, but you don't show any SAS format in the DEFINE statement that would format the numbers with a COMMA format. In addition, your IF statement treats M1% as numeric with a value of .95 and .90, etc, but again, there's no format being used in the PROC REPORT step to format the decimal number as a percent.

  Is it possible to see your SAS log that's generating the  results you posted?  I would be curious to see how your reference to M1%, M2%, etc is being handled. 

  There are a few different ways to handle having the percent columns with the same background color as the M1, M2 and M3 variables. One way is to use another CALL DEFINE in the COMPUTE block and an alternate way is to use traffic-lighting with a user-defined format for the percent variables and then a simplified COMPUTE block for the M1, M2 and M3 values.

Cynthia

 

  With some fake data and different variable names, I think either of these examples will produce the report you want. Note that the second example has a simplified COMPUTE block, but the first example also works, although it is more verbose. ( I only typed a few rows of data since I had to make adjustments for the < and the % in the example data you posted.)

Cynthia_sas_0-1707753542259.png

 

 

 

NagendraBS
Fluorite | Level 6

Thanks a lot. Yes i did messed up in creating the fake data along with columns, but yeah got it right. 

 

jeanleid
Calcite | Level 5

Hi,

Try adding this additional syntax to your compute blocks:

 

 

if M1% > .95 then do; 

         call define ('M1', "style","style={background=light green}");

          call define (_row_,'style','style={background=light green}';

         end ;

 

Then repeat this code in the other percentage variables.

Good luck!

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

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
  • 3 replies
  • 182 views
  • 1 like
  • 3 in conversation