BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## Re: count and merge

I will choose the second one (7th obs) because it belong to the Percantage=100 from Veh dataset (6th obs),

usually the Percentage =(0,100) or (25,75) which is the blame percentage of crash, to distinguish who is guilty or innocent (from Veh dataset) where the crash involve single vehicle or multiple vehicles. (Par dataset) includes the participants info. e.g PrtyType (Driver, Passenger, pedestrian ...).Therfore, when there are (two entries in the Par dataset, both of them are driver) meaning there should be two vehicles involved in the crash. First one 0% and the second 100% in this example.

Barite | Level 11

## Re: count and merge

veh:

8799176    34969    100    -    0    -    Front    31

par:

8799176    34969    0    Driver    intact    Other    31
8799176    34969    0    Driver    intact    SD    31

There is no way to see that the second record in par goes with that record from Veh.

Quartz | Level 8

## Re: count and merge

veh:

8799176    34969    100    -    0    -    Front    31

par:

8799176    34969    0    Driver    intact    Other    31
8799176    34969    0    Driver    intact    SD    31

I want SAS process in order, so if the first record of percentage (fom Veh) not equal =100, then it should not choose first (Driver from Par).

Barite | Level 11

## Re: count and merge

Here is a solution, in the last 'WANT' dataset you provided you left out InjNo and VehNo, they are in my final dataset.  I'm guessing you wanted to stay consistent with ommitting requirements throughout:

proc sql;
create table acc_prep as
select *,sum(InjMinor,InjurMajor) as InjNo
from acc
order by key_id,kroki;

proc sort data=veh;by key_id kroki;

data veh_count;
set veh;
by key_id kroki;
count+1;
Vehcount+1;
if first.kroki then do;
vehcount = 1;
count = 1;
end;
run;

proc sql;
create table veh_prep as
select *,max(vehcount) as VehNo
from veh_count
group by kroki
order by key_id,kroki;

proc sort data=par;by key_id kroki;

data par_prep;
set par;
by key_id kroki;
count+1;
if first.kroki then count = 1;
run;

data final_prep;
merge veh_prep (in=a where=(percentage = 100))
par_prep (in=b);
by key_id kroki count;
if a and b;
run;

data final_merge;
merge acc_prep  (in=a)
final_prep(in=b);
by key_id kroki;
if a and b;
run;

proc sql;
create table want as
select distinct kroki,key_id,accidenttime,acctype,accpoint,wlight,severity,publicdamage,accreasons,accidentangle,injurmajor,deaths,injminor,
branchid,percentage,vehiclemake,vehiclemodel,vehiclecolor,accidentplace,age,partytype,healthstatus,nationality,InjNo,vehno
from final_merge
order by kroki,key_id;

Quartz | Level 8

## Re: count and merge

Dear @Steelers_In_DC

I appreciate your effort and time,

I found an error When i used the code for my datasets

(ERROR: BY variables are not properly sorted on data set WORK.VEH_PREP.
)

Barite | Level 11

## Re: count and merge

The veh_prep dataset I have is ordered correctly.  It seems that you did not copy/paste correctly.

Quartz | Level 8

## Re: count and merge

Yes you are right, I thoought that because (count) need to be ordered so,
I added (proc sort data=veh_prep;by key_id kroki count;run;) and it works, but I found some of (PartyType is not 'Driver') and i could not find the (ParNo)

as following :

data par_prep;
set par;
by key_id kroki;
count+1;
if first.kroki then count = 1;
run;

proc sort data=veh_prep;by key_id kroki count;run;

data final_prep;
merge veh_prep (in=a where=(percentage = 100))
par_prep (in=b);
by key_id kroki count;
if a and b;
run;

Barite | Level 11

## Re: count and merge

I'm keeping the variables you said you wanted in your initial question.  The drop statement is in the set statement, dropping the column from the input, not the output.

Quartz | Level 8

## Re: count and merge

dear @Steelers_In_DC

i appreciate your help and understanding,

what i mean is there are other variables like age needs to be added, also i need to add (parno) like you did with (vehno)

Barite | Level 11

## Re: count and merge

Here's another solution, just so we are clear, that is not in your desired dataset.  We've spent more time going back and forth because you don't know how to ask a question and explain what it is you want:

proc sql;
create table acc_prep as
select *,sum(InjMinor,InjurMajor) as InjNo
from acc
order by key_id,kroki;

proc sort data=veh;by key_id kroki;

data veh_count;
set veh;
by key_id kroki;
count+1;
Vehcount+1;
if first.kroki then do;
vehcount = 1;
count = 1;
end;
run;

proc sql;
create table veh_prep as
select *,max(vehcount) as VehNo
from veh_count
group by kroki
order by key_id,kroki;

proc sort data=par;by key_id kroki;

data par_count;
set par;
by key_id kroki;
count+1;
Parcount+1;
if first.kroki then do;
count = 1;
parcount = 1;
end;
run;

proc sql;
create table par_prep as
select *,max(parcount) as ParNo
from par_count
group by kroki
order by key_id,kroki;

data final_prep;
merge veh_prep (in=a where=(percentage = 100))
par_prep (in=b);
by key_id kroki count;
if a and b;
run;

data final_merge;
merge acc_prep  (in=a)
final_prep(in=b);
by key_id kroki;
if a and b;
run;

proc sql;
create table want as
select distinct kroki,key_id,accidenttime,acctype,accpoint,wlight,severity,publicdamage,accreasons,accidentangle,injurmajor,deaths,injminor,
branchid,percentage,vehiclemake,vehiclemodel,vehiclecolor,accidentplace,age,partytype,healthstatus,nationality,InjNo,vehno,parno
from final_merge
order by kroki,key_id;

Quartz | Level 8

## Re: count and merge

Dear @Steelers_In_DC

You are right, in my dataset the (PartyType contains driver, pedestrian ....) that what I tried to explain to you. I just sorted by count then created new dataset where PartyType=driver, then it works, so thanks a lot

I realy appreciate your help.

Quartz | Level 8

## Re: count and merge

dear @ballardw

i have the data set as excel where there is no missing of BRANCHID, but i provided it here as (input) as a smple.

Barite | Level 11

## Re: count and merge

I made some assumptions but I think this is close:

data acc;
infile datalines dlm = " ";
input Kroki Key_ID Severity \$ InjurMajor Deaths InjMinor BranchId;
datalines ;
8505804 36412 Damage 0 0 0 31
8581794 36412 Deaths 0 2 0 32
8530886 36412 Damage 0 0 0 34
8536144 38802 Damage 0 0 0 32
8528435 38802 Damage 0 0 0 31
9324143 38802 Damage 0 0 0 34
8525667 38802 Deaths 0 2 0 33
9344281 40400 Damage 0 0 0 34
9357307 40400 Deaths 0 2 0 31
8503111 40400 Damage 0 0 0 33
8538023 32675 Deaths 1 34
8595057 32675 Damage 0 0 0 32
8733084 33281 Deaths 3 1 0 31
8643633 33281 Damage 0 0 0 34
8449397 33281 Damage 0 0 0 32
8743224 38323 Damage 0 0 0 32
8517348 38323 Damage 0 0 0 33
9345729 38323 Damage 0 0 0 34
8544720 38323 Deaths 0 1 0 31
9325908 40503 Damage 0 0 0 31
9336147 40503 Damage 0 0 0 34
9352576 40503 Serious injuries 1 0 0 33
9352537 40663 Damage 0 0 0 33
9359019 40663 Damage 0 0 0 34
9353437 40663 Minor injuries 0 0 1 31
;run;

proc sort data=acc;by key_id kroki;

data acc_prep;
set acc;
by key_id kroki;
DeathsNo+deaths;
if first.key_id then DeathsNo = deaths;
InjNo=sum(injurmajor,injminor);
run;

data veh;
infile datalines dlm = " ";
input Kroki Key_ID Percentage BranchId;
datalines;
8505804 36412 100 31
8505804 36412 0 31
8530886 36412 100 34
8530886 36412 0 34
8581794 36412 100 32
8525667 38802 100 33
8528435 38802 0 31
8528435 38802 100 31
8536144 38802 0 32
8536144 38802 0 32
9324143 38802 100 34
9324143 38802 0 34
8503111 40400 100 33
8503111 40400 0 33
9344281 40400 100 34
9344281 40400 0 34
9357307 40400 0 31
9357307 40400 100 31
8538023 32675 0 34
8538023 32675 100 34
8595057 32675 0 32
8595057 32675 100 32
8449397 33281 0 32
8449397 33281 100 32
8643633 33281 100 34
8643633 33281 0 34
8733084 33281 100 31
8517348 38323 100 33
8517348 38323 0 33
8544720 38323 100 31
8743224 38323 0 32
9345729 38323 0 34
9345729 38323 100 34
9325908 40503 0 31
9325908 40503 0 31
9325908 40503 100 31
9336147 40503 100 34
9336147 40503 0 34
9352576 40503 75 33
9352576 40503 25 33
9352537 40663 100 33
9352537 40663 0 33
9353437 40663 0 31
9353437 40663 100 31
9359019 40663 100 34
9359019 40663 0 34
;run;

proc sort data=veh;by key_id kroki;

data veh_prep;
set veh;
by key_id kroki;
VehNo+1;
if first.key_id then vehno = 1;
run;

proc sql;
create table max_veh as
select kroki,key_id,Percentage,max(vehno) as VehNo
from veh_prep
group by key_id
order by key_id,kroki;

data par;
infile datalines dlm = " ";
input Kroki Key_ID Age PartyType \$ HealthStatus \$ BranchId;
datalines;
8505804 36412 0 Driver intact 31
8505804 36412 0 Driver intact 31
8530886 36412 0 Driver intact 34
8581794 36412 0 Driver intact 32
8581794 36412 0 Passenger Death in hospital 32
8581794 36412 0 Passenger Death in hospital 32
8525667 38802 0 Driver Death in hospital 33
8528435 38802 0 Driver intact 31
8528435 38802 0 Driver intact 31
8536144 38802 0 Driver intact 32
8536144 38802 0 Driver intact 32
9324143 38802 0 Driver intact 34
9324143 38802 0 Driver intact 34
8525667 38802 0 Passenger Death in hospital 33
8503111 40400 0 Driver intact 33
8503111 40400 0 Driver intact 33
9344281 40400 0 Driver intact 34
9344281 40400 0 Driver intact 34
9357307 40400 24 Driver Death at the site 31
9357307 40400 39 Driver intact 31
9357307 40400 16 Passenger Death at the site 31
8538023 32675 0 Driver Other 34
8595057 32675 0 Driver intact 32
8595057 32675 0 Driver intact 32
8538023 32675 0 Runaway driver Death in hospital 34
8449397 33281 0 Driver intact 32
8449397 33281 0 Driver intact 32
8643633 33281 0 Driver intact 34
8643633 33281 0 Driver intact 34
8733084 33281 35 Driver Death at the site 31
8733084 33281 30 Passenger Taken to hospital 31
8733084 33281 0 Passenger Taken to hospital 31
8733084 33281 0 Passenger Taken to hospital 31
8517348 38323 0 Driver intact 33
8517348 38323 0 Driver intact 33
8544720 38323 54 Driver Death at the site 31
8544720 38323 0 Driver Other 31
8743224 38323 0 Driver intact 32
9345729 38323 0 Driver intact 34
9325908 40503 0 Driver intact 31
9325908 40503 0 Driver intact 31
9325908 40503 0 Driver intact 31
9336147 40503 0 Driver intact 34
9336147 40503 0 Driver intact 34
9352576 40503 0 Driver Taken to hospital 33
9352576 40503 0 Driver intact 33
9352537 40663 0 Driver intact 33
9352537 40663 0 Driver intact 33
9353437 40663 0 Driver Treated on-site 31
9353437 40663 0 Driver intact 31
9359019 40663 0 Driver intact 34
9359019 40663 0 Driver intact 34
;run;

proc sort data=par;by key_id kroki;

data want(keep=Kroki Key_ID BranchId Severity InjNo DeathsNo VehNo) ;
merge acc_prep(in=a)
max_veh(in=b where=(percentage=100))
par(in=c where=(PartyType='Driver'));
by key_id kroki;
if a;
run;

Discussion stats
• 27 replies
• 1975 views
• 1 like
• 3 in conversation