Combine / Collapse Binary Variables By..

[Jsendzik]

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 B	Flag C
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

Desired Output Data:

Student	Flag A	Flag B	Flag C
Carl	1	1	0
Tito	1	1	1

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

Thanks!

[Tom · Accepted solution]

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;

[Jsendzik]

Thank you

[UrvishShah]

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;

        /*Based on your requirement, add more flags*/

   end;

run;

-Urvish