Date: Thu, 16 Apr 2009 14:52:51 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: Pick the closest date to reference date
In-Reply-To: <ba0020da-2438-43a2-a7ea-d1849b8ac052@r3g2000vbp.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1
A double DOW loop solution. I assume you want to get min. difference by ID
PARAMETER to show interesting results; if you have a different requirement,
adjust the BY statement and LAST./FIRST. appropriately.
data have;
informat trtdt refdt MMDDYY10.;
input id parameter visit trtdt refdt;
datalines;
101 1 1 4/1/2009 4/14/2009
101 1 2 4/2/2009 4/14/2009
101 1 3 4/5/2009 4/14/2009
101 1 4 4/6/2009 4/14/2009
101 1 5 4/7/2009 4/14/2009
101 1 6 4/8/2009 4/14/2009
101 1 7 4/9/2009 4/14/2009
101 1 8 4/17/2009 4/14/2009
101 1 9 4/19/2009 4/14/2009
101 1 10 4/20/2009 4/14/2009
101 1 11 4/21/2009 4/14/2009
101 1 12 4/23/2009 4/14/2009
101 2 1 4/5/2009 4/14/2009
101 2 2 4/6/2009 4/14/2009
101 2 3 4/7/2009 4/14/2009
101 2 4 4/8/2009 4/14/2009
101 2 5 4/9/2009 4/14/2009
101 2 6 4/10/2009 4/14/2009
101 2 7 4/11/2009 4/14/2009
101 2 8 4/12/2009 4/14/2009
101 2 9 4/13/2009 4/14/2009
101 2 10 4/14/2009 4/14/2009
101 2 11 4/15/2009 4/14/2009
101 2 12 4/16/2009 4/14/2009
;;;;
run;
data want;
retain keepdt diff_min;
do _n_ = 1 by 1 until (last.parameter);
set have;
by id parameter;
if first.parameter then do;
keepdt=0;
diff_min =99999 ;
end;
diff = abs(refdt-trtdt);
if missing(diff_min) or (diff < diff_min) or (diff = diff_min and
trtdt>refdt) then do;
diff_min = diff;
keepdt = trtdt;
end;
end;
do _n_ = 1 by 1 until (last.parameter);
set have;
by id parameter;
if trtdt = keepdt then output;
end;
run;
I'm fairly sure there should be a simpler solution using PROC SQL but I was
struggling with HAVING MIN ( ) for some reason [I think that would be the
way to go?] ...
-Joe
On Thu, Apr 16, 2009 at 2:31 PM, kasa <venkatrkasa@gmail.com> wrote:
> i would like to pick the closest date to the reference date
> if the two dates are equi-distant from reference date then I would
> like to pick later date.
> Thanks in advance for your reply.
>
>
> id parameter visit trtdt refdt
> 101 1 1 4/1/2009 4/14/2009
> 101 1 2 4/2/2009 4/14/2009
> 101 1 3 4/5/2009 4/14/2009
> 101 1 4 4/6/2009 4/14/2009
> 101 1 5 4/7/2009 4/14/2009
> 101 1 6 4/8/2009 4/14/2009
> 101 1 7 4/9/2009 4/14/2009
> 101 1 8 4/17/2009 4/14/2009
> 101 1 9 4/19/2009 4/14/2009
> 101 1 10 4/20/2009 4/14/2009
> 101 1 11 4/21/2009 4/14/2009
> 101 1 12 4/23/2009 4/14/2009
> 101 2 1 4/5/2009 4/14/2009
> 101 2 2 4/6/2009 4/14/2009
> 101 2 3 4/7/2009 4/14/2009
> 101 2 4 4/8/2009 4/14/2009
> 101 2 5 4/9/2009 4/14/2009
> 101 2 6 4/10/2009 4/14/2009
> 101 2 7 4/11/2009 4/14/2009
> 101 2 8 4/12/2009 4/14/2009
> 101 2 9 4/13/2009 4/14/2009
> 101 2 10 4/14/2009 4/14/2009
> 101 2 11 4/15/2009 4/14/2009
> 101 2 12 4/16/2009 4/14/2009
>
