Help using Base SAS procedures

Proc SQL Help Dealing with Resubmits

Reply
Contributor
Posts: 37

Proc SQL Help Dealing with Resubmits

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?

Super User
Posts: 19,850

Re: Proc SQL Help Dealing with Resubmits

Posted in reply to AAWTomHanks

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.

Contributor
Posts: 37

Re: Proc SQL Help Dealing with Resubmits

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.

Super User
Posts: 19,850

Re: Proc SQL Help Dealing with Resubmits

Posted in reply to AAWTomHanks

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.

Frequent Contributor
Posts: 95

Re: Proc SQL Help Dealing with Resubmits

Posted in reply to AAWTomHanks

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;

LINENAMEDDATEFVPVSUMRESUBMITLINE_BRESUBMIT_DAYS
1NEW8-Mar-11127979.518-Mar-11410
1NEW8-Mar-11127979.513-Mar-1135
2NEW13-Mar-11126403.197-Apr-11525
2NEW13-Mar-11126403.1918-Mar-1145
3NEW18-Mar-11125378.487-Apr-11520
4NEW6-Apr-111228029.587-Apr-1151
Ask a Question
Discussion stats
  • 4 replies
  • 168 views
  • 0 likes
  • 3 in conversation