topic Re: Help with a Merge in SAS Programming

Help with a Merge

UCFtigers2017 — Tue, 31 Oct 2017 20:11:09 GMT

I am looking for help with how to do what I believe is a many - to - one merge (if possible).

I have 2 data sets :

A. car crashes (each observation is a crash)

B. car vehicles involved in a crash (each observation is a car in one of the crashes in first data set)

I have an indicator called "distract" in data set B that determines whether the driver of that car was distracted while driving.

0 = not distracted

1 = distracted

I want a merge into data set A so that that the crash takes on the value of 1 if ANY of the cars involved in that crash had a distracted driver.

Another way that could be acceptable, is the "distract" variable in the new merged set can take on the value of the SUM of distracted drivers involved in the crash.

Thanks!

in case you need to know, they are merging by caseid year psu .

Re: Help with a Merge

Reeza — Tue, 31 Oct 2017 20:17:57 GMT

This is much easier if you provide sample data we can work with. But the concept is still the same, calculate a summary statistic and merge in with the full data. You may be able to do this summarizing the accident data to determine the number of distracted drivers per accident and then merging that summary data.

Re: Help with a Merge

ballardw — Tue, 31 Oct 2017 22:52:54 GMT

Maybe something similar to:

proc sql;
   create table want as
   select a.*, max(b.distracted) as AnyDistracted,
          sum(b.Distracted) as TotalDistracted
   from crashset as a
        left join
        vehicleset a b
        on a.caseid=b.caseid
        and a.year =b.year
        and a.psu  =b.psu
  group by a.crashid,a.year,a.psu
  ;
quit;