DATA Step, Macro, Functions and more

Help with Proc Format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Help with Proc Format

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.


Accepted Solutions
Solution
‎10-10-2011 04:28 PM
Super User
Super User
Posts: 7,079

Re: Help with Proc Format

Posted in reply to ChrisHumana

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


All Replies
Solution
‎10-10-2011 04:28 PM
Super User
Super User
Posts: 7,079

Re: Help with Proc Format

Posted in reply to ChrisHumana

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

Occasional Contributor
Posts: 5

Help with Proc Format

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.

Frequent Contributor
Posts: 82

Re: Help with Proc Format

Posted in reply to ChrisHumana

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.

Occasional Contributor
Posts: 5

Help with Proc Format

Posted in reply to ChrisHumana

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

Valued Guide
Posts: 765

Re: Help with Proc Format

Posted in reply to ChrisHumana

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

Occasional Contributor
Posts: 5

Help with Proc Format

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;

Super User
Super User
Posts: 7,079

Help with Proc Format

Posted in reply to ChrisHumana

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);

Occasional Contributor
Posts: 5

Help with Proc Format

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 313 views
  • 6 likes
  • 4 in conversation