Keep closest date?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Keep closest date?

I have two datasets - one with a statement date (sm_date) and the other with a payment date (paydate2). I've also created a variable called close to associate the payment to the closest statement date with this proc SQL:

proc sql;

    create table finaldsn as

select a.*

, b.*

, a.paydate2-b.sm_date as days

, abs(calculated days) as absdays

, min(calculated absdays)as close

from dates_payment as a

full join

stmts_dates as b

on a.acct_no=b.acct_no

where a.paydate2 ne . and b.sm_date ne .

and b.acct_no in (select distinct acct_no

from dates_payment)

group by a.acct_no, a.paydate2

having calculated absdays=calculated close

;

quit;

Now I want to only keep those records where it's the minimum value for "close" - i.e. only keep that one record on the payment file with the min(close) and delete the other records with the same acct_no. But this code is vomiting on me:

data test;

    set finaldsn;

    keep acct_no where min(close);

    run;

Help?


Accepted Solutions
Solution
‎09-13-2013 10:46 AM
Super User
Posts: 19,817

Re: Keep closest date?

Posted in reply to aprilewilson

I think you're looking for first. logic instead. So if you and an order by to your sql process to order the dates appropriately you can use something like the following:

I'm not 100% sure because I can't see your data, but this may work.

proc sql;

    create table finaldsn as

select a.*

, b.*

, a.paydate2-b.sm_date as days

, abs(calculated days) as absdays

, min(calculated absdays)as close

from dates_payment as a

full join

stmts_dates as b

on a.acct_no=b.acct_no

where a.paydate2 ne . and b.sm_date ne .

and b.acct_no in (select distinct acct_no

from dates_payment)

group by a.acct_no, a.paydate2

having calculated absdays=calculated close

order by acct_no, paydate, close

;

quit;

data test;

     set finaldsn;

by acct_no paydate;

if first.acct_no;

run;

View solution in original post


All Replies
Solution
‎09-13-2013 10:46 AM
Super User
Posts: 19,817

Re: Keep closest date?

Posted in reply to aprilewilson

I think you're looking for first. logic instead. So if you and an order by to your sql process to order the dates appropriately you can use something like the following:

I'm not 100% sure because I can't see your data, but this may work.

proc sql;

    create table finaldsn as

select a.*

, b.*

, a.paydate2-b.sm_date as days

, abs(calculated days) as absdays

, min(calculated absdays)as close

from dates_payment as a

full join

stmts_dates as b

on a.acct_no=b.acct_no

where a.paydate2 ne . and b.sm_date ne .

and b.acct_no in (select distinct acct_no

from dates_payment)

group by a.acct_no, a.paydate2

having calculated absdays=calculated close

order by acct_no, paydate, close

;

quit;

data test;

     set finaldsn;

by acct_no paydate;

if first.acct_no;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 854 views
  • 0 likes
  • 2 in conversation