SAS DATES

[robertrao]

Hi

I was trying to acheive this but have some confusion in writing the code..

Could you please help me resolve

TABLE1

ID       place_time                     counter

101  06JUN2013:04:50:000        1
101  06JUN2013:04:57:000        2
101  11JUN2013:17:45:000        3
101  11JUN2013:19:38:000        4

TABLE2

ID   remove_time

101  11JUN2013:13:35:000
101  15JUN2013:14:30:000

WANT  

                      placed_time              removed_time          counter

101     06JUN2013:04:57:000     11JUN2013:13:35:000      1
101     11JUN2013:19:38:000     15JUN2013:14:30:000      4

/*remove time have to be greater than the place time and both has to be closer to each other!!!!*/
/*Thats the reason in the want 06JUN2013:04:57:000 in contrast to 06JUN2013:04:50:000

/*Also there could be many variations for the data like :
several place times and several remove times for the same ID

or

several placed times and only one removed time

or

several placed times and no removed times

or

several removed times and no placed time etc etc

Thanks

[AncaTilea]

So, to understand correctly,

why did you pick two records for this one ID?

The closest place-to-remove time would be the 11JUN2013:19:38:000    to this remove_time 11JUN2013:13:35:000,

I don't understand, from your example, why the 6th of June was included?

Well, here is what I had in mind:

data table1;

format place_time EURDFDT.;

  id = 101;

  counter = 1;

  place_time = '06JUN2013:04:50:000'dt;

  output;

  id = 101;counter = 2;

  place_time = '06JUN2013:04:57:000'dt;

  output;

  id = 101;counter = 3;

  place_time = '11JUN2013:17:45:000'dt;

  output;

  id = 101;counter = 4;

  place_time = '11JUN2013:19:38:000'dt;

  output;

run;

data table2;

format remove_time EURDFDT.;

  id = 101;

  remove_time = '11JUN2013:13:35:000'dt;

  output;

  id = 101;

  remove_time = '15JUN2013:14:30:000'dt;

  output;

run;

proc sql;

  create table want as

  select a.id, a.place_time, b.remove_time,(b.remove_time - a.place_time) as diff, a.counter

  from table1 a join table2 b

  on table1.id = table2.id

  where b.remove_time > a.place_time

  having diff le min(diff);

quit;

I am confused :smileyconfused:

[robertrao]

First, the remove data is on 11JUN2013:13:35:000 , so the placement date should be prior to this and must be very close also .
Prir to this there are 06JUN2013 at 4:50 and 4:57.and 4:57 will be the closest to 11JUN2013:13:35
As you can see the 11JUN dates are occuring after 13:35 so they cannot be prior to

ID       place_time         counter      remove_time           is remove>place     very clsoe to each other?

101  06JUN2013:04:50:000        1    11JUN2013:13:35:000          yes                  no
101  06JUN2013:04:57:000        2    11JUN2013:13:35:000          yes                  yes(7min close when compared to the above so pick this pair)
101  11JUN2013:17:45:000        3    11JUN2013:13:35:000          no
101  11JUN2013:19:38:000        4    11JUN2013:13:35:000          no

NEXT

ID       place_time         counter      remove_time           is remove>place     very clsoe to each other?

101  06JUN2013:04:50:000        1    15JUN2013:14:30:000           yes                  no
101  06JUN2013:04:57:000        2    15JUN2013:14:30:000           yes                  no
101  11JUN2013:17:45:000        3    15JUN2013:14:30:000           yes                  no
101  11JUN2013:19:38:000        4    15JUN2013:14:30:000           yes                  yes(so pick this pair)

[PaigeMiller]

Since have typed the solution you want as an example, then following what you have typed, the solution should be obvious. Merge the two tables, compute the delta in time between place and remove, sort, choose the one where the delta is smallest given that remove>place.

[robertrao]

Hi , Could you help me with the code in simple SAS data steps?

Also when I merge I get the Message "Merge has more than 2 datasets with repeats of BY variables!!!1

Thanks

[PaigeMiller]

data both;

     merge table1 table2;

     by id;

     delta_time=remove_time-place_time;

     if delta_time<0 then delete;

run;

proc sort data=both;

     by id delta_time;

run;

data final;

     set both;

     by id;

     if first.id;

run;

[robertrao]

Hi,

One last question.

Does this code work for all the cases I mentioned above???

/*Also there could be many variations for the data like :
several place times and several remove times for the same ID

or

several placed times and only one removed time

or

several placed times and no removed times

or

several removed times and no placed time etc etc

Thanks

[PaigeMiller]

Probably only works for the case where there is one removed time.

[robertrao]

Hi,

How can we extend this to a condition say one placed time and 3 removal times????:

placed                     removed

09JUN2013           10JUN2013:10AM

                               10JUN2013:11AM

                               12JUN2013

[AncaTilea]

I understand now.

So, you could use a verison of this:

proc sql;

  create table want as

  select a.id, a.place_time, b.remove_time,(b.remove_time - a.place_time) as diff, a.counter

  from table1 a join table2 b

  on table1.id = table2.id

  where b.remove_time > a.place_time;

  having diff le min(diff);

quit;

and then...take out the records you don't want...

...