Help with Proc Format

[ChrisHumana]

Hello I am using PROC FORMAT to change the value of a zscore to a integer.  Here is an example:

VALUE AVG_MBR_CHRG_ZSCORE 

. = 0

LOW - 1.19 = 0

1.2 - 1.5 = 1

1.51 - 2.2 = 2

2.21< - HIGH = 3;

Everything links up in my data correctly when I apply the format.  For example, if the zscore is 1.25 it displays the label of 1.  The problem I am having is I want to add up all the weights into a final score.  It seems to be pulling from the original value versus the format label when doing the addition.  Does anyone have advice or experience on how to get around this?

Any help would be greatly appreciated.

[Tom · Accepted solution]

You probably need to create a new variable that has the value implied by the format.

Also your format has holes in it.  What value do you want for Z=1.195 ?

proc format ;

VALUE Z2INT

   ._ - .Z = 0

  low - 1.2 = 0

  1.2 - 1.5 = 1

  1.5 - 2.2 = 2

  2.2 - high = 3

;

run;

data have ;

  missing a b c d e f g h i j k l m n o p q r s t u v w x y z _;

  input z @@ ;

  int = inputn(put(z,z2int1.),1.);

  put z 7.3 +1 int ;

cards;

. _ A Z -1 0 1 1.19 1.2 1.21 1.5 1.51 2.2 2.21 400

run;

   .    0
   _    0
   A    0
   Z    0
 -1.000 0
  0.000 0
  1.000 0
  1.190 0
  1.200 0
  1.210 1
  1.500 1
  1.510 2
  2.200 2
  2.210 3
400.000 3

[ChrisHumana]

Thanks Tom.

I didn't think of that for this particular situation.  I am going to try that today.  I have the zscore result set to decimal.2 so I shouldn't have to worry about extending into the 3rd decimal place.  Hopefully I am not overlooking something.

[ieva]

Agree with Tom that if you want to use these formated values, new variable should be created.

variable2=put(variable, avg_mbr_chrg_zscore.);

Then everything should work as you expected.

[ChrisHumana]

Thanks again Tom and Ieva for responding.  Everything worked as expected.

[MikeZdeb]

Hi ...some ideas:  could change Tom's suggested FORMAT to an INFORMAT; change the intervals; add the carats to take care of values that might fall between the various limits of the ranges ...

proc format ;

invalue z2int

  1.19<- 1.5  = 1

  1.5 <- 2.2  = 2

  2.2 <- high = 3

  other = 0

;

run;

data have ;

  input z @@ ;

  int = input(cat(z),z2int.);

datalines;

. _ A Z -1 0 1 1.19 1.195 1.2 1.21 1.495 1.5 1.51 2.2 2.21 400

run;

     z    int

   .        0

   _        0

   A        0

   Z        0

-1.000     0

  0.000     0

  1.000     0

  1.190     0

  1.195     1

  1.200     1

  1.210     1

  1.500     1

  1.510     2

  2.200     2

  2.210     3

  2.220     3

400.000     3

[ChrisHumana]

Good point Mike. 

I have the zscore values set to decimal.2 so I didn't think I had to account for anything past that.  I set the value of my zscore based on my formats listed below.  Then I do a conversion to numeric.  Then I do a final IF check before I do a sum of all my different zscore weights.  Reason for that is my final data has 5390 providers in it and not all of them will have the zscore values.  So when I do my final left join some of them show up as blank if they weren't in the data set joined in.  The numbers weren't coming out correctly when adding a '.' into a series of integers.  Setting all the blanks to zero fixed that.

Here is some of my code.

VALUE AVG_MBR_CHRG_ZSCORE

. = 0

LOW - 1.19 = 0

1.2 - 1.5 = 1

1.51 - 2.2 = 2

2.21 <- HIGH = 3;

AVG_MBR_CHRG_WEIGHT = INPUTN(PUT(AVG_MBR_CHRG_ZSCORE, 

AVG_MBR_CHRG_ZSCORE.), 2.);

IF AVG_MBR_CHRG_WEIGHT = . THEN DO;

     AVG_MBR_CHRG_WEIGHT = 0;

END;

[Tom]

With that format you should not have missing values of ZSCORE being convert to missing values of WEIGHT.

What values of ZSCORE are generating missing values?

Why are you sure that the values of ZSCORE are rounded to two decimal places?

As you noticed with the problem that started this thread just appying a format will not change the values.

What command did you use to create the value?

If you read them from a text string that only included 2 decimal places then it should be ok.

Otherwise you can use the round function.  x=round(x, 0.01);

[ChrisHumana]

Hey Tom,

Thanks again for your input.  I see what you mean about the rounding.  I have rounded all zscores to accordingly.  The only reason I have missing values for some of the zscores is due to some calculations do not apply to certain providers.  For example, if looking at the total charged for DME supplies not all providers have billed those supplies.  Joining data back will result in no match, which I substitute with a zero to avoid math errors.  I used the round function to take care of all zscore rounding. 

Thanks again.