Proc SQL Help Dealing with Resubmits

[AAWTomHanks]

PROC SQL;
 CREATE TABLE RESUBMITS AS
   SELECT *, (RESUBMIT-DDATE) as RESUBMIT_DAYS
   FROM A(KEEP=PV FV NAME DDATE SUM) a, A(KEEP=PV FV NAME ADATE RENAME=(ADATE=RESUBMIT)) b
   WHERE a.NAME=b.NAME AND a.FV=b.FV AND a.PV=b.PV
    HAVING ((RESUBMIT > DDATE) AND (RESUBMIT-DDATE < 30));
QUIT;

I could use some help I created this code but it is not letting me do what I really need. This is a little bit of my data:

LINE       NAME   ADATE                  DDATE                  FV           PV          SUM

1              NEW      02/27/2011         03/08/2011         1              2              $7,979.50

2              NEW      03/08/2011         03/13/2011         1              2              $6,403.19

3              NEW      03/13/2011         03/18/2011         1              2              $5,378.48

4              NEW      03/18/2011         04/06/2011         1              2              $28,029.58

5              NEW      04/07/2011         04/16/2011         1              2              $11,702.64

For instance the code I have above is counting line 3 and line 2 as resubmits of line 1 but I do not want that. I only want line 2 to be counted as a resubmit of line 1. Anyone have any suggestions?

[Reeza]

Is the rule that it's only a resubmit if the adate=the previous ddate ? I also get the impression there should be another identifier (ie account/client) of sorts.

[AAWTomHanks]

It is only a resubmit if the ADATE is within 30 days of the DDATE. In my case above, I need line 3 to only be a resumit of line 2, instead the code that I created counts it as a resubmit of both line 1 and line 2.

[Reeza]

Well it is, according to the rules you've specified, ie within 30 days and after the initial. Are you counting only the first resubmit?

You can try googling readmission as this sounds similar to 30 day readmission problems that come up often.

[Alpay]

Do ADATE and DDATE fields have time parts?

Line 2 has ADATE of 03/08/2011 same value as DDATE of Line 1. In that case, (RESUBMIT > DDATE) criteria won't be true.

Instead, Line 3 and 4 would be returmed. I have run your code with addition of Line numbers from a and b.

PROC SQL;

CREATE TABLE RESUBMITS AS

   SELECT *, B.LINE as LINE_B, (RESUBMIT-DDATE) as RESUBMIT_DAYS

   FROM A(KEEP=LINE PV FV NAME DDATE SUM) a, A(KEEP=LINE PV FV NAME ADATE RENAME=(ADATE=RESUBMIT)) b

   WHERE a.NAME=b.NAME AND a.FV=b.FV AND a.PV=b.PV

    HAVING ((RESUBMIT > DDATE) AND (RESUBMIT-DDATE < 30));

QUIT;

LINE	NAME	DDATE	FV	PV	SUM	RESUBMIT	LINE_B	RESUBMIT_DAYS
1	NEW	8-Mar-11	1	2	7979.5	18-Mar-11	4	10
1	NEW	8-Mar-11	1	2	7979.5	13-Mar-11	3	5
2	NEW	13-Mar-11	1	2	6403.19	7-Apr-11	5	25
2	NEW	13-Mar-11	1	2	6403.19	18-Mar-11	4	5
3	NEW	18-Mar-11	1	2	5378.48	7-Apr-11	5	20
4	NEW	6-Apr-11	1	2	28029.58	7-Apr-11	5	1