BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8

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

9 REPLIES 9
AncaTilea
Pyrite | Level 9

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
Quartz | Level 8

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
Diamond | Level 26

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.

--
Paige Miller
robertrao
Quartz | Level 8

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
Diamond | Level 26

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;

--
Paige Miller
robertrao
Quartz | Level 8

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
Diamond | Level 26

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

--
Paige Miller
robertrao
Quartz | Level 8

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
Pyrite | Level 9

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...

...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1064 views
  • 0 likes
  • 3 in conversation