DATA Step, Macro, Functions and more

subsetting data based on datetime from another file

Reply
Super Contributor
Posts: 312

subsetting data based on datetime from another file

In data A, I have game player data: 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;

 

Contributor
Posts: 39

Re: subsetting data based on datetime from another file

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

Ask a Question
Discussion stats
  • 1 reply
  • 169 views
  • 0 likes
  • 2 in conversation