topic Re: Proc SQL Help Dealing with Resubmits in SAS Procedures

Proc SQL Help Dealing with Resubmits

AAWTomHanks — Wed, 06 Jun 2012 19:40:17 GMT

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?

Re: Proc SQL Help Dealing with Resubmits

Reeza — Wed, 06 Jun 2012 20:08:43 GMT

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.

Re: Proc SQL Help Dealing with Resubmits

AAWTomHanks — Wed, 06 Jun 2012 20:33:53 GMT

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.

Re: Proc SQL Help Dealing with Resubmits

Reeza — Wed, 06 Jun 2012 22:03:35 GMT

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.

Re: Proc SQL Help Dealing with Resubmits

Alpay — Fri, 08 Jun 2012 04:29:40 GMT

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