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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.