topic How to perform calculations in a detail line SAS data set and then put the results into new database in SAS Programming

How to perform calculations in a detail line SAS data set and then put the results into new database

wlierman — Thu, 30 Aug 2018 20:26:53 GMT

This is the challenge that I am am facing:

I have a small (880000 obs) data set. There are a minimum of 4 unique claim numbers for each recipient, so this 880K data set is my working calculation set (with a minimum of 4 claims up to, I think there is one person with 77 claim lines!) I want to calculate a Bice - Boxer man index for each recipient and then put (write) the result, along with the Recipient ID to a new data set. This data set will have (using hypothetical numbers) say 2,500 distinct recipients each with a B-B Index value. The B-B index values vary between 0 and 1 inclusive. I haven't successfully worked this out - thus, I am here - but this rough schematic shows one recipient with multiple claim lines in the working calculation set. Incidentally I am counting up the number of primary care providers to use in the B-B index over each block of claims.

ID Claim_No PCP_ID

123453 897605 40000

123453 245168 40000

123453 589741 40000

123453 589456 54123

123456 659421 65874

123456 568971 21356

123456 474895 32150

123456 412008 32150

123456 568888 32150

123456 213004 32150

There are 5 distinct PCP providers here. The B-B Index is calculated as follows with individual provider j, s number of providers

and n visits (distinct claims)

∑ n^2 - n

j=1 to s n(n-1)

using this formula the B-B index for recipient 123456 is calculated as: B-B = (3^sq - 3) + (1^sq - 1) + (1^sq - 1) + (1^sq - 1)

+(4^sq - 4) / (5)*(4)

= 6 + 0 + 0 + 0 +12 / 20

= 18/20

= 0.9

Then I would like to put the each ID value and corresponding calculated B-B index to a new data set

New data set

obs ID B-B index

1 123456 0.9

. . . . . .

2500 345267 0.23

That is what I am trying to do. I apologize that I don't have any active code, but I can say that the program (which started with many million obs) is littered with false starts and incorrect results.

Any tips, helps, techniques from the community of experienced users are welcomed.

Walt Lierman

Re: How to perform calculations in a detail line SAS data set and then put the results into new data

ballardw — Thu, 30 Aug 2018 21:30:28 GMT

You formula really needs to show two different n's, one for the n of individual providers and the N of number of providers.

One way:

data have;
   input id $ claim_no $ PCP_id $;
datalines;
123456  897605  40000 
123456  245168  40000
123456  589741  40000
123456  589456  54123
123456  659421  65874
123456  568971  21356
123456  474895  32150
123456  412008  32150
123456  568888  32150
123456  213004  32150
;
run;

proc summary data=have nway ;
  class id pcp_id;
  output out=sum1 ;
run;
proc summary data=sum1 nway;
   class id;
   output out=sum2;
run;

data temp;
  merge  sum1 (rename=(_freq_=n))
         sum2 (rename=(_freq_=bign))
  ;
  by id;
  val = (n**2 - n)/ (bign * (bign-1));
run;

proc summary data=temp nway;
   class id;
   var val;
   output out=want (drop=_:) sum=B_B_index;
run;

Note that your example data has two different ID values and so does not actually match you manual calculations. I "fixed" the ID on the first 4 records.

Proc summary without any VAR variable gives a count. The counts could be done with proc SQL or proc freq as well.

Re: How to perform calculations in a detail line SAS data set and then put the results into new data

wlierman — Thu, 30 Aug 2018 22:49:19 GMT

Hello ballardw,
You nailed it! Thank you very much for the code and catching my typo (ID value).
This really breaks a log-jam.
Take care.

Cheers,

W Lierman

Re: How to perform calculations in a detail line SAS data set and then put the results into new data

novinosrin — Thu, 30 Aug 2018 23:54:07 GMT

HI @wlierman Sorry for the bother. Would be nice and courteous and ethical if you could give the credit to @ballardw by marking his answer as the accepted solution. Thank you!