topic Re: SAS DATES in SAS Procedures

SAS DATES

robertrao — Wed, 07 Aug 2013 17:06:05 GMT

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

several placed times and only one removed time

several placed times and no removed times

several removed times and no placed time etc etc

Thanks

Re: SAS DATES

AncaTilea — Wed, 07 Aug 2013 19:03:16 GMT

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:

Re: SAS DATES

robertrao — Wed, 07 Aug 2013 19:31:11 GMT

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

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)

Re: SAS DATES

PaigeMiller — Wed, 07 Aug 2013 19:53:56 GMT

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.

Re: SAS DATES

AncaTilea — Wed, 07 Aug 2013 20:02:52 GMT

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

...

Re: SAS DATES

robertrao — Wed, 07 Aug 2013 20:04:49 GMT

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

Re: SAS DATES

PaigeMiller — Wed, 07 Aug 2013 20:09:31 GMT

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;

Re: SAS DATES

robertrao — Wed, 07 Aug 2013 20:26:37 GMT

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

several placed times and only one removed time

several placed times and no removed times

several removed times and no placed time etc etc

Thanks

Re: SAS DATES

PaigeMiller — Thu, 08 Aug 2013 12:44:04 GMT

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

Re: SAS DATES

robertrao — Thu, 08 Aug 2013 14:58:09 GMT

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