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