Fluorite | Level 6

Combine / Collapse Binary Variables By..

Hello world,

I need some help collapsing data into a single row. I've created a data set that creates a flag for the test each student has taken (see table). The problem is I need this data to be one row per student and can't figure out how to code this properly. I've provided an example below.

Original Data:

Student Test Flag A Flag BFlag C
Carl A 100
Carl B010
Tito A100
TitoB 010
TitoC001

Desired Output Data:

Student Flag A Flag B Flag C
Carl 110
Tito111

Is there a sas procedure I could use in this situation? Any help would be much appreciated.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Combine / Collapse Binary Variables By..

Take the maximum for each flag over all records for that student.

proc means data=have nway ;

by student;

var flag: ;

output want  max= ;

run;

3 REPLIES 3
Super User

Re: Combine / Collapse Binary Variables By..

Take the maximum for each flag over all records for that student.

proc means data=have nway ;

by student;

var flag: ;

output want  max= ;

run;

Fluorite | Level 6

Re: Combine / Collapse Binary Variables By..

Thank you

Fluorite | Level 6

Re: Combine / Collapse Binary Variables By..

Hi,

Try with the following code...Although it is hard coded at one place to recreate the flags,it meets your requirement...

data have;

input student \$ test \$ flag1 - flag3;

cards4;

Carl A 1 0 0

Carl B 0 1 0

Tito A 1 0 0

Tito B 0 1 0

Tito C 0 0 1

;;;;

proc sql noprint;

create table have1 as

select student,count(student) as no_of_student

from have

group by 1;

select max(no_of_student) into :max

from have1;

quit;

data want(drop = i);

set have1;

array flag(&max.);

do i = 1 to &max.;

flag(i) = 1;

if no_of_student < &max. then do;

flag3 = 0;

end;