DATA Step, Macro, Functions and more

Dynamic compare columns within groups

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Dynamic compare columns within groups

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;

 


Accepted Solutions
Solution
‎10-28-2015 09:59 AM
Contributor
Posts: 23

Re: Dynamic compare columns within groups

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,919

Re: Dynamic compare columns within groups

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;
PG
Solution
‎10-28-2015 09:59 AM
Contributor
Posts: 23

Re: Dynamic compare columns within groups

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
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 204 views
  • 1 like
  • 2 in conversation