Dear SAS Folks,
I have a question on comparing two columns from two data sets named past_evt and fut_evt.
Both data sets were sorted by city. I need to compare the column named ‘city’ within each area + ship_time group between past_evt with fut_evt. Flag any ‘city’ that appeared in past_evt but not in fut_evt. Please keep in mind that the comparison should be conducted only within groups (ie. Area+ship_time), not across group.
My thought is using first_variableNme and last_variableNme is a possible method for this question, but I don’t know how to do it. Is anybody can give me some suggestions?
Thanks,
Best regards,
Harry
The following is a sample of my data sets.
data past_evt;
input city $ area $ ship_time TIME8.;
cards;
Brookf TX 21:37:55
Cherok CA 9:36:18
CHILLI TX 23:50:14
FRN WA 18:45:23
FRN WA 13:21:11
FRN WA 4:30:24
GALVES CO 21:38:18
MARCEL IL 13:38:06
NEEDLE FL 12:24:40
Ravena NJ 3:25:48
Ravena NJ 6:23:10
SEATTL NY 9:19:13
SEATTL NY 10:56:02
STCOI TX 21:37:55
STJOMO TX 9:36:18
;
RUN;
data FUT_evt;
input city $ area $ ship_time TIME8.;
cards;
Brookf TX 21:37:55
Cherok CA 9:36:18
CHILLI TX 23:50:14
LAFAYE CO 6:32:51
MARCEL IL 19:32:16
MARCEL IL 2:19:37
Ravena NJ 3:25:48
Ravena NJ 6:23:10
STJOMO TX 9:36:18
;
RUN;
SQL to the rescue:
proc sql;
select * from past_evt as a
where not exists(
select * from fut_evt
where city=a.city and area=a.area and ship_time=a.ship_time)
;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.