Dynamic compare columns within groups

HarryLiu — Tue, 27 Oct 2015 17:25:03 GMT

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;

Re: Dynamic compare columns within groups

PGStats — Tue, 27 Oct 2015 17:55:03 GMT

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;

Re: Dynamic compare columns within groups

HarryLiu — Wed, 28 Oct 2015 13:58:24 GMT

Dear PGStats,
Thanks very much for your quick response. I like this code, since it is simple and concise. I appreciate your time very much!
Thanks,
Harry

topic Re: Dynamic compare columns within groups in SAS Programming

Dynamic compare columns within groups

Re: Dynamic compare columns within groups

Re: Dynamic compare columns within groups