Date:         Tue, 19 Oct 2010 22:21:48 -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: Hash or No-Hash solution to my problem?
Comments: To: Arthur Tabachneck <art297@netscape.net>, raghavadimulam@westat.com
In-Reply-To:  <201010192300.o9JH5kFd012853@willow.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

I'll present a slightly different version of the SQL solution, which I find
the most appealing (this is what SQL is very good at).  The double DoW loop
is basically the same thing but in SAS and longer to code.

Data NOHash;
input MRN $3.  DateDiff;
cards;
001 500
001 400
001 300
001 200
001 100
001 0
002 500
002 400
002 300
002 200
002 100
003 700
003 300
003 200
003 50
;;;;
run;
proc sql noprint;
create table want as
    select N.* from nohash N, (select MRN, min(datediff) as min_datediff
from nohash H group by MRN) H
    where H.MRN=N.MRN and N.DateDiff <= H.min_DateDiff+365
    order by MRN,  datediff descending;
quit;


Although this is functionally identical to Art's but longer coding, it is
more standard; Art's uses group by in a way that's not standard, and while
it does work in SAS it won't work in Oracle, TSQL (Microsft SQL), or most
other locations, and I find myself getting in trouble not using more
standard SQL the more I start doing DB programming directly :)

-Joe

On Tue, Oct 19, 2010 at 6:00 PM, Arthur Tabachneck <art297@netscape.net>wrote:

> Raghav,
>
> I think you can get what you want with some fairly simply proc sql code.
> For example:
>
> proc sql noprint;
>  create table want
>    as select *
>      from have
>        group by MRN
>          having DateDiff <= min(DateDiff)+365
> ;
> quit;
>
> HTH,
> Art
> ---------
> On Tue, 19 Oct 2010 18:42:33 -0400, Raghav Adimulam
> <raghavadimulam@WESTAT.COM> wrote:
>
> >Hello SAS-Lers,
> >
> >Here is the simplified version of my problem, i.e. without the unnecessary
> >fields and misc data. I have a dataset with 2 fields that is sorted on MRN
> >and DateDiff as shown below.
> >
> >MRN DateDiff
> >001 500
> >001 400
> >001 300
> >001 200
> >001 100
> >001 0
> >
> >002 500
> >002 400
> >002 300
> >002 200
> >002 100
> >
> >003 700
> >003 300
> >003 200
> >003 50
> >
> >How can I accomplish a task so that I can keep only the records that fall
> >within 365 days of the lowest DateDiff for that MRN??
> >
> >In other words, MRN 001 has qualifying records <=0+365
> >MRN 002 has qualifying records <=100+365 (=465)
> >MRN 003 has qualifying records <=50+365 (=415)
> >
> >Thus the final dataset should be..
> >
> >MRN DateDiff
> >001 300
> >001 200
> >001 100
> >001 0
> >
> >002 400
> >002 300
> >002 200
> >002 100
> >
> >003 300
> >003 200
> >003 50
> >
> >Thanks for your answers in advance!
> >
> >-Raghav
>