Multiple if statment

[Sandeep77]

Hi All,

I am using multiple if statement to decide the score bandings but it gives 'Good' as a result to all the scores. If I add else after the score bands i.e., poor, fair, good etc, then it gives only Very Poor as the result in score band. Can you please check what I need to change to correct the code? Thanks

Data Score_bandings;
set Latest_TUScores;
if score < 550 then Score_Band = "Very Poor";
if score < 565 then score_band = "Poor";
if score < 605 then score_band = "Fair";
if score < 627 then score_band = "Good";
else
score_band = "Excellent";
run;

Log:
29         Data Score_bandings;
30         set Latest_TUScores;
31         if score < 550 then Score_Band = "Very Poor";
32         if score < 565 then score_band = "Poor";
33         if score < 605 then score_band = "Fair";
34         if score < 627 then score_band = "Good";
35         else
36         score_band = "Excellent";
37         run;

NOTE: Variable score is uninitialized.
NOTE: There were 12521397 observations read from the data set WORK.LATEST_TUSCORES.
NOTE: The data set WORK.SCORE_BANDINGS has 12521397 observations and 11 variables.
NOTE: Compressing data set WORK.SCORE_BANDINGS decreased size by 34.08 percent. 
      Compressed is 14133 pages; un-compressed would require 21441 pages.
NOTE: DATA statement used (Total process time):
      real time           16.46 seconds
      user cpu time       7.95 seconds
      system cpu time     0.92 seconds
      memory              1125.78k
      OS Memory           27992.00k
      Timestamp           12/06/2022 10:22:40 AM
      Step Count                        24  Switch Count  29
      

38         
39         %LET _CLIENTTASKLABEL=;
40         %LET _CLIENTPROCESSFLOWNAME=;

[PaigeMiller · Accepted solution]

NOTE: Variable score is uninitialized.

This is a problem. You definitely need to check into this.

Your logic should be (in words, not actual SAS code)

if something then action1
else if something then action2
else if something then action3
else action4

[Astounding]

Adding "else" is correct. However the bigger problem is that your data set does not contain a variable named SCORE. Perhaps you are reading from the wrong data set. Perhaps the spelling should be different. Either way, start by finding SCORE.

[Sandeep77]

Thank you. I understood my mistake and now got the score from the correct column. I was using the wrong column to pull the score bandings. Thanks again.

[ballardw]

You might consider using custom formats instead of adding text variables to your data.

Proc format;
value score_band
low -< 550 = 'Very Poor'
550 -< 565='Poor'
565 -< 605='Fair'
605 -< 627='Good'
627 - high ='Excellent'
;
run; 

Use by assigning the format when needed.

proc freq data=Latest_TUScores;
   tables score_variable;
   format score_variable score_band.  ;
run;

Note that the format becomes more useful when you may have multiple variables that you want to display the same values as you can use the format with multiple variables. Also, since the underlying values are still numeric the order for tables like freq would be "Very Poor" "Poor" "Fair" "Good" "Excellent". As shown your value order when using a character variable would be "Excellent" "Fair" "Good" "Poor" "Very Poor".

Another advantage to formats is that when the boss comes in and says "What happens when we change the bands to ..." . Then the only thing needed is to either modify the existing format or create a different one and use it. No adding variables.

The groups created with the formats will be honored by reporting and analysis procedures and most will work in graphs(exceptions are usually custom date/time/datetime formats).

Plus if you make a minor change to the label such as changing "Very Poor" to be "Lowest" or similar or the number of ranges in the bands, again only changes to the format are needed. No need to modify a bunch of code in data sets to change the display text.

If you may have similar needs for different variables in different data sets guess what, no change to the data set needed. Just use the format with the other variable in the other set.

Formats also avoid a lot of the If / then/ else code and the syntax of the value ranges indicate open or closed ranges so you can tell which value an endpoint will display in easier than with if/then/else.

The limitation on formats is that they use single variables.