Help with a Merge

Reply
New Contributor
Posts: 4

Help with a Merge

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 .

Super User
Posts: 21,530

Re: Help with a Merge

Posted in reply to UCFtigers2017
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.
Super User
Posts: 12,148

Re: Help with a Merge

Posted in reply to UCFtigers2017

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; 
Ask a Question
Discussion stats
  • 2 replies
  • 82 views
  • 0 likes
  • 3 in conversation