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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

8 REPLIES 8
Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Pyrite | Level 9

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
Calcite | Level 5

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

MikeZdeb
Rhodochrosite | Level 12

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 8 replies
  • 1464 views
  • 6 likes
  • 4 in conversation