topic Re: Conditional first. & last. in SAS Programming

Conditional first. & last.

e_______c — Wed, 15 Apr 2020 02:55:33 GMT

Hi 🙂

I want to create a conditional variable (outcome) based on accident_id and road_user_type:

- if anyone in an accident was a vulnerable road user > then outcome = 1;

- else if everyone in an accident was a MVO > then outcome = 2;

- else outcome = 3.

Please help 🙂

dataset have;

Accident_ID	Road_user_type
1	Vulnerable
1	MVO
1	.
2	.
2	MVO
3	MVO
3	MVO
3	MVO

dataset want;

Accident_ID	Road_user_type	Outcome
1	Vulnerable	1
1	MVO	1
1	.	1
2	.	.
2	MVO	.
3	MVO	2
3	MVO	2
3	MVO	2

Re: Conditional first. & last.

sdhilip — Wed, 15 Apr 2020 05:12:23 GMT

Data Want;
Set Have;
If Road_user_type = "Vulnerable" then Outcome = 1;
If Road_user_type = "MVO" then Outcome = 2;
Else Outcome = 3;
Run;

Re: Conditional first. & last.

ChrisNZ — Wed, 15 Apr 2020 06:26:58 GMT

Why are there three ones?

Re: Conditional first. & last.

e_______c — Wed, 15 Apr 2020 06:31:17 GMT

Hey Chris,

This is road crash data. The three one means the three people (rows) are each in the same accident (accident_id). Does that make sense?

Re: Conditional first. & last.

kelxxx — Wed, 15 Apr 2020 07:22:01 GMT

data want;
set have;
by accident_id;
retain outcome;
if first.accident_id then do;
if road_user_type eq "Vulnerable" then outcome=1;
else if road_user_type eq "MVO" then outcome=2;
else if road_user_type eq "" then outcome=.;
else outcome=3;
end;
run;

Hello, it s my proposition.

I saw in the case accident_id = 2, road_user_type =. >>> outcome =. >>> then I add a case where outcome =.
So we have 4 cases: outcome =., 1,2,3

Have a nice day.

Re: Conditional first. & last.

Shmuel — Wed, 15 Apr 2020 07:50:34 GMT

data have;
 infile datalines;
 input Accident_ID Road_user_type $10.;
datalines;
1 Vulnerable
1 MVO
1 .
2 .
2 MVO
3 MVO
3 MVO
3 MVO
3 MVO
; run;
 
 data temp1;
 set have;
       if road_user_type = 'Vulnerable' then road_user_code=1; else
       if road_user_type = 'MVO' then road_user_code=2; else
         road_user_code=0;
run;

proc sort data=temp1; by Accident_ID road_user_code; run;

data temp2;
 set temp1;
    by Accident_ID ;
       retain outcome;
       if first.Accident_ID then outcome=.;
       if road_user_code = 1 then outcome=1;
       if outcome ne 1 then do;
          if first.Accident_ID then do;
             if road_user_code = 2
                then outcome = 2;
                else outcome = .;
          end; 
          else do;
             if road_user_code = 2 and
                outcome = 2 then;
             else outcome = .;
          end;
      end;
      if last.accident_id then output;
run;

data want;
 merge have temp2;
  by Accident_ID ;
run;

Re: Conditional first. & last.

ed_sas_member — Wed, 15 Apr 2020 07:51:21 GMT

Hi @e_______c

Here is another approach using a PROC SQL:

proc sql;

	/* if anyone in an accident was a vulnerable road user > then outcome = 1 */
	(select *, 1 as outcome
	from have
	group by Accident_ID
	having sum(Road_user_type="Vulnerable") > 0)
	
	union all
	
	/* else if everyone in an accident was a MVO > then outcome = 2 */
	(select *, 2 as outcome
	from have
	group by Accident_ID
	having count(Accident_ID) = sum(Road_user_type="MVO"))

	union all
	
	/* else outcome = 3 */
	(select *, 3 as outcome
	from have
	group by Accident_ID
	having sum(Road_user_type="Vulnerable") = 0 and count(Accident_ID) > sum(Road_user_type="MVO"))
	
	order by Accident_ID;
quit;

Re: Conditional first. & last.

Kurt_Bremser — Wed, 15 Apr 2020 07:52:03 GMT

Per your rule, accident_id = 2 should have outcome 3.

This code achieves that:

data have;
input Accident_ID Road_user_type :$10.;
datalines;
1 Vulnerable
1 MVO
1 .
2 .
2 MVO
3 MVO
3 MVO
3 MVO
;

data acc_type;
set have;
by accident_id;
retain flag_mvo flag_vuln;
if first.accident_id
then do;
  flag_mvo = 1;
  flag_vuln = 0;
end;
if road_user_type ne 'MVO' then flag_mvo = 0;
if road_user_type = 'Vulnerable' than flag_vuln = 1;
if last.accident_id;
if flag_vuln then outcome = 1;
else if flag_mvo then outcome = 2;
else outcome = 3;
keep accident_id outcome;
run;

data want;
merge
  have
  acc_type
;
by accident_id;
run;

Re: Conditional first. & last.

ChrisNZ — Wed, 15 Apr 2020 12:08:05 GMT

Like this?

proc sql;
  select ACCIDENT_ID
       , ROAD_USER_TYPE 
       , case when sum(ROAD_USER_TYPE='Vulnerable')   then 1
              when sum(ROAD_USER_TYPE='MVO')=count(*) then 2
              else                                         3 end as OUTCOME
  from HAVE
  group by ACCIDENT_ID;


ACCIDENT_ID	ROAD_USER_TYPE	OUTCOME
1		1
1	Vulnerable	1
1	MVO	1
2	MVO	3
2		3
3	MVO	2
3	MVO	2
3	MVO	2

Re: Conditional first. & last.

mkeintz — Wed, 15 Apr 2020 17:21:16 GMT

You have a nice compact assignment rule, whose data needs can be satisfied with a self-merge, where each argument (except the last) of the merge statement selects a needed data subset:

data have;
input Accident_ID Road_user_type :$10.;
datalines;
1 Vulnerable
1 MVO
1 .
2 .
2 MVO
3 MVO
3 MVO
3 MVO
;

data want;
  merge 
      have (where=(road_user_type='Vulnerable') in=invul)
      have (where=(road_user_type=' ') in=inmiss)
      have (where=(road_user_type^='MVO') in=non_mvo)
      have ;
  by accident_id;
  if invul=1   then outcome=1; else
  if inmiss=1  then outcome=.; else
  if non_mvo=0 then outcome=2; else
  outcome=3;
run;

You might ordinarily be worried about mixing data from differing observations in the merge. But because the last argument of the merge is the complete set of records in HAVE, the individual values from those records will overwrite the value obtained from the matching subsets preceding it.

edit: Let me add some further notes on the MERGE statement with BY. Although the merged subsets for each ID can have differing numbers of records from each other and from the full dataset, the IN= dummies will be constant for the entire BY-group. I.e. if you have one record with road_user_type=' ' (and therefore inmiss=1) vs 3 records in the entire BY group, you'll have 3 iterations of the data step (i.e. 3 output records in this case), and for ALL of those iterations, the INMISS will equal 1.

Re: Conditional first. & last.

e_______c — Thu, 16 Apr 2020 01:41:14 GMT

Thanks mkeintz > that worked perfectly! Such a simple thing but I couldn't figure it out. Thanks again!