Join where date from A within 92 days of date in B

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 83
Accepted Solution

Join where date from A within 92 days of date in B

hello,

Help with the following would be really appreciated:

 

I'd like to do a SQL join where the date from table A should join with date in table B within 92 days (or 3 months) or less

 

[do not join if table b date is 93 days or more past date from table A)

 

here is the starter code

proc sql;  create table want as
  select distinct a.*, b.date as date2
    from file1 as a left outer join file2 as b  on
        a.id_no=b.id_no and  a.date=b.date; quit;

Could I modify line 4 to achieve the above?  Both dates formatted date9.

Thanks in advance


Accepted Solutions
Solution
‎08-10-2016 02:25 PM
Frequent Contributor
Posts: 83

Re: Join where date from A within 92 days of date in B

Update: I achived what I was looking for by adding the following to line 4: and a.date between b.date and b.date + 92

 

proc sql;  create table want as
  select distinct a.*, b.date as date2
    from file1 as a left outer join file2 as b  on
       a.id_no=b.id_no and  a.date between b.date and b.date + 92; quit;

View solution in original post


All Replies
Respected Advisor
Posts: 4,955

Re: Join where date from A within 92 days of date in B

Your DATE variables must be actual SAS dates, for this to work ... not character strings and not date-time values.  Then you could try:

 

where a.id_no = b.id_no and a.date <= b.date and b.date <= a.date + 92

Frequent Contributor
Posts: 83

Re: Join where date from A within 92 days of date in B

Thank you for the tip
Respected Advisor
Posts: 3,823

Re: Join where date from A within 92 days of date in B

You will get the best answers if you also post sample data and expected result.

 

Is below what you're after?

data file1;
  format date date9.;
  date=today();
  do id_no=1 to 3;
    output;
  end;
run;

data file2;
  set file1;
  date=intnx('month',date,id_no+1,'s');
run;

proc sql;
  create table want as
    select distinct a.*, b.date as date2
    from 
        file1 as a left outer join file2 as b  
          on 
            a.id_no=b.id_no 
            /* within 3 months into the future */
            and  b.date between a.date and intnx('month',a.date,3,'s');
            /* whithin 3 months past and future, so 6 month period in total */
/*            and  b.date between intnx('month',a.date,-3,'s') and intnx('month',a.date,3,'s');*/
quit;

In case you're dealing with SAS datetime values use 'dtmonth' instead of 'month' in function intnx() - or wrap function datepart() around the variable.

 

Frequent Contributor
Posts: 83

Re: Join where date from A within 92 days of date in B

[ Edited ]

hi Patrick,

I apologize for not including sample data / expected results.

 

Example data from dataset A (file1)

 

ID           DATE           LINEFROM   LINETO

01          01JUL2016    $1000         $2000

01          01JUL2016    $2000         $3000

02          01JUL2016     $1000        $ 500    

 

Example data from  dataset B (file2)

 

ID           DATE                 LETTERSENT

01          01MAR2015                Y

01          01MAR2016                Y

02          01 JUN2016                 Y

 

Expected result: Only ID02 from B would join to A dataset, as B date is within 92 days of A date.

 

There are millions of records in each datasets, which I cannot modify.

 

Thanks again,

Grand Advisor
Posts: 10,196

Re: Join where date from A within 92 days of date in B

Is there a typo in your dataset B?

01          02MAR2016                Y

is the same date for one of the ID 01 records in dataset a.

Frequent Contributor
Posts: 83

Re: Join where date from A within 92 days of date in B

Oh, thanks ballardw- just corrected it
Solution
‎08-10-2016 02:25 PM
Frequent Contributor
Posts: 83

Re: Join where date from A within 92 days of date in B

Update: I achived what I was looking for by adding the following to line 4: and a.date between b.date and b.date + 92

 

proc sql;  create table want as
  select distinct a.*, b.date as date2
    from file1 as a left outer join file2 as b  on
       a.id_no=b.id_no and  a.date between b.date and b.date + 92; quit;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 386 views
  • 1 like
  • 4 in conversation