subsetting data based on datetime from another file

fengyuwuzu — Fri, 22 Apr 2016 15:27:53 GMT

In data A, I have game player data&colon; ID, play_datetime, amount.

001 1/10/2014 5:37:00 AM 10

001 1/21/2014 7:46:05 AM 20

001 2/11/2014 5:36:00 AM 20

001 3/09/2014 9:65:10 AM 15

001 3/18/2014 3:36:00 AM 20

001 4/14/2014 7:05:21 AM 20

001 4/21/2014 6:19:10 AM 22

001 5/15/2014 9:34:08 AM 13

...

002 ...

...

in Data B, I have ID, limit_setting_datetime

001 3/27/2014 4:26:11 PM

002 4/11/2014 9:24:08 AM

003 4/10/2014 6:37:09 AM

So, what I want is, either to subset data A, or add a new column with "before" or "after" to differentiate data before or after the date_time in data B, like for ID 001 in Data A, it compares datetime in A to 001 3/27/2014 4:26:11 PM in Data B, and generate:

001 1/10/2014 5:37:00 AM 10 Before

001 1/21/2014 7:46:05 AM 20 Before

001 2/11/2014 5:36:00 AM 20 Before

001 3/09/2014 9:65:10 AM 15 Before

001 3/18/2014 3:36:00 AM 20 Before

001 4/14/2014 7:05:21 AM 20 After

001 4/21/2014 6:19:10 AM 22 After

001 5/15/2014 9:34:08 AM 13 After

I am thinking about proc sql, and use "case when " options, but wonder if there is a better solution.

/* to add one more column in data_A, "before_after" to as time indicator. */

proc sql;
   create table data_A_with_time_indicator as
   select a.*, case
                when a.datetime <= b.datetime then "Before"
                when a.datetime > b.datetime then "After"
                else 'None'
                end as Before_after
      from Data_A as a Data_B as b;
quit;

Re: subsetting data based on datetime from another file

Pamela_JSRCC — Fri, 22 Apr 2016 16:19:37 GMT

looks good to me, except that I think you want a left join on ID to avoid losing any rows from dataA with an ID not found in dataB:

from dataA A

left join daraB B on b.id = a.id

topic subsetting data based on datetime from another file in SAS Programming

subsetting data based on datetime from another file

Re: subsetting data based on datetime from another file