topic Re: pick the closest prior date in SAS Programming

pick the closest prior date

sam369 — Mon, 18 Jul 2016 22:35:55 GMT

Dear All,

I have two datesets, one is have1 dataset, which has id and date. and another dataset is have2 which has id and cdate. i need to pick the closest prior cdate to date. here is sample to better understand.

here is my try:

proc sql;
create table want as
select a.*
from have1 as a left join have2 as b on a.id=b.id and a.date >= b.date
group by a.id;
quit;

data have1;
input id date ;
100 18SEP2013
100 25SEP2013
100 04OCT2013
100 11OCT2013
100 25OCT2013
100 08NOV2013
100 22NOV2013
100 06DEC2013
100 20DEC2013
100 08JAN2014
100 17JAN2014
100 31JAN2014
100 17FEB2014
100 28FEB2014
100 14MAR2014
100 28MAR2014
100 04APR2014
100 02MAY2014
;;;
run;

data have2;
input id cdate;
100 04OCT2013
100 07OCT2013
100 09OCT2013
100 11OCT2013
100 14OCT2013
100 16OCT2013
100 18OCT2013
100 21OCT2013
100 23OCT2013
100 25OCT2013
100 28OCT2013
100 30OCT2013
100 01NOV2013
100 04NOV2013
100 06NOV2013
100 08NOV2013
100 11NOV2013
100 13NOV2013
100 15NOV2013
100 18NOV2013
100 20NOV2013
100 22NOV2013
100 25NOV2013
100 27NOV2013
100 29NOV2013
100 02DEC2013
100 04DEC2013
100 06DEC2013
100 08DEC2013
100 11DEC2013
100 13DEC2013
100 16DEC2013
100 18DEC2013
100 20DEC2013
100 23DEC2013
100 26DEC2013
100 28DEC2013
100 30DEC2013
100 02JAN2014
100 04JAN2014
100 06JAN2014
100 08JAN2014
100 10JAN2014
100 13JAN2014
100 15JAN2014
100 17JAN2014
100 20JAN2014
100 22JAN2014
100 24JAN2014
100 27JAN2014
100 29JAN2014
100 31JAN2014
100 03FEB2014
100 05FEB2014
100 07FEB2014
100 10FEB2014
100 12FEB2014
100 14FEB2014
100 17FEB2014
100 19FEB2014
100 21FEB2014
100 24FEB2014
100 26FEB2014
100 28FEB2014
100 03MAR2014
100 05MAR2014
100 07MAR2014
100 10MAR2014
100 12MAR2014
100 14MAR2014
100 17MAR2014
100 19MAR2014
100 21MAR2014
100 24MAR2014
100 26MAR2014
100 28MAR2014
100 31MAR2014
100 02APR2014
;;;

data want;
input id date cdate;
100 18SEP2013 .
100 25SEP2013 .
100 04OCT2013 .
100 11OCT2013 09OCT2013
100 25OCT2013 23OCT2013
100 08NOV2013 06NOV2013
100 22NOV2013 20NOV2013
100 06DEC2013 04DEC2013
100 20DEC2013 18DEC2013
100 08JAN2014 06JAN2014
100 17JAN2014 15JAN2014
100 31JAN2014 29JAN2014
100 17FEB2014 14FEB2014
100 28FEB2014 26FEB2014
100 14MAR2014 12MAR2014
100 28MAR2014 26MAR2014
100 04APR2014 02APR2014
100 02MAY2014 02APR2014
;;;
run;

Thanks

Sam

Re: pick the closest prior date

Astounding — Mon, 18 Jul 2016 23:01:11 GMT

I'm sure SQL can handle this, but I find it easier to use a DATA step. Assuming both data sets are sorted:

data want;

set have2 (rename=(cdate=date) in=in2)

have1 (in=in1);

by id date;

if first.id then cdate=.;

retain cdate;

if in1 then output;

if in2 then cdate=date;

run;

If both data sets contain the same date, this program selects that date as CDATE. If that's not right and CDATE must be prior to DATE, you would need to switch the order of the data sets in the SET statement:

set have1 (in=in1)

have2 (rename=(cdate=date) in=in2);

Re: pick the closest prior date

sam369 — Mon, 18 Jul 2016 23:15:37 GMT

Thank you astounding,

But the ouput results are not what i expected, according to your suggested code. same date is getting for cdate and date. i am looking for closest prior date

for record 100 date from have1 11oct2013 and cdate should be priod date of date that means 09oct2013

Thanks

Sam

Re: pick the closest prior date

stat_sas — Mon, 18 Jul 2016 23:55:00 GMT

Hi,

Please see below a sql solution:

proc sql;
create table want as
select a.*, cdate from have1 a
left join have2 b
on cdate<date
group by date
having (date-cdate)=min(date-cdate)
order by date;
quit;

Re: pick the closest prior date

sam369 — Tue, 19 Jul 2016 00:01:59 GMT

Thank you both!!!

Thanks

Sam

Re: pick the closest prior date

Astounding — Tue, 19 Jul 2016 00:18:14 GMT

Hmmmm ....

Did you try switching the order in the SET statement, like I mentioned at the bottom of my post?

Re: pick the closest prior date

sam369 — Tue, 19 Jul 2016 00:23:35 GMT

Hi Astounding,

Intially i did not look that part, later i checked and it worked !!! thank you so much for the help

Thanks

Sam