SAS Coding

[Kiyai]

HI guys,

I seek your help on how to code below. I've been searching on-line and most of the time found calculating difference between 2 rows not so much on tracking the values in 2 rows and use it as basis to calculate other variables.

In this example, i'm trying to calculate RSQN by tracking the value between 2 rows namely, outresv and cumpaid.

Whereas, OCC_NO is using claim & RSQN.

For the actual formula, I've attached the excel

Thank you in advance for your expert assistance.

[mkeintz]

Why does sequence #10 have RSQN=4?  Shouldn't it be a 1 (with a corresponding increment to OCC_NO)?  After all the prior record (seq #9) has outresv cumpaid=0, which is putatively the signal to end one OCC_NO and start the next. 

And in sequence number 5 for the second claim, that is exactly what you do.  You reset RSQN=1 and increment OCC_NO from 1 to 2.

This program does that:

data have;
  input Claim $8.  SEQNO  OUTRESV :comma8.2  CUMPAID :comma8.2;
datalines;
A1696384	1	2,085.90	0.00
A1696384	2	2,642.30	0.00
A1696384	3	859.49	1,782.81
A1696384	4	0.00	1,782.81
A1696384	5	500.76	1,782.81
A1696384	6	0.00	2,283.57
A1696384	7	624.00	2,283.57
A1696384	8	62.44	2,845.53
A1696384	9	0.00	2,845.53
A1696384	10	0.00	2,845.53
A1821392	1	200	0
A1821392	2	0	200
A1821392	3	200	0
A1821392	4	0	0
A1821392	5	0	0
;
data want;
  set have;
  by claim;
  RSQN+1;

  if lag(outresv)=0 or first.claim=1 then do;
    RSQN=1;
    OCC_NO+1;
    if first.claim then OCC_NO=0;
  end;
run;

This program uses "summing statement" expressions, like the statement   "RSQN+1"  which says to 

1. Increment RSQN by +1
2. But don't reset RSQN to missing upon returning to the top of the DATA step.   

The longer if statement checks whether the record-in-hand is the start of a new OCC  (i.e. follows a OUTRESV=0) or start of a new claim number.  In both cases it increments OCC_NO, but in the case of a new claim, it immediately resets OCC_NO to 0.

[Kiyai]

Hi mkeintz, 

Thanks for you advise.

The above data is example of claims (insurance) data related to transactions on visit by patient (can be inpatient/outpatient to hospital/clinic)

Claim	SEQNO	OUTRESV	CUMPAID	GPAY	RSQN	OCC_NO
A1696384	1	2,085.90	0.00	0.00	1	00
A1696384	2	2,642.30	0.00	0.00	2	00
A1696384	3	859.49	1,782.81	1,782.81	3	00
A1696384	4	0.00	1,782.81	0.00	4	00

When a patient visit to hospital/clinic,

1) When a claim is recorded, certain amount of reserve is set aside to pay the claim

2) Then the reserve is review (reduce/increase) upon receiving the actual bill from hospital/clinic

3) The bill will be check and subsequently payment were made and reserve review (reduce/increase)

4) Reserve review and reduce to 0. Then, the claim is consider closed.

Based on the above, seqno cannot be used. therefore, i've to come out with RSQN.

The first 4 transactions related to '00' or 1st visit (most likely inpatient).

Secondly,

Claim	SEQNO	OUTRESV	CUMPAID	GPAY	RSQN	OCC_NO
A1696384	5	500.76	1,782.81	0.00	1	01
A1696384	6	0.00	2,283.57	500.76	2	01

5) '01' is consider as second visit (outpatient@follow-up). Similar to the first visit process.

6) Bill is check, payment made and reserve review and reduce to 0 . Then, the claim is consider closed.

Then, RSQN will restart due to new visit and the reserve put in and eventually payment will be made and closed.

2 transactions related to visit '01'.

Thirdly,

Claim	SEQNO	OUTRESV	CUMPAID	GPAY	RSQN	OCC_NO
A1696384	7	624.00	2,283.57	0.00	1	02
A1696384	8	62.44	2,845.53	561.96	2	02
A1696384	9	0.00	2,845.53	0.00	3	02
A1696384	10	0.00	2,845.53	0.00	4	02

Seqno 7, 8, 9 & 10 similar to visit '01' (another outpatient). However seqno 9 & 10 can be amendment to the detail of the claims and the reduction of reserve to 0. The last 4 transactions related to visit '02'.

Therefore inside the excel file, RSQN is checking the outresv and cumpaid to come out with the RSQN.

Occ_no is checking claim and rsqn.

Hope the above explain.

Thanks again, i'll try.