topic Re: Combine / Collapse Binary Variables By.. in SAS Programming

Combine / Collapse Binary Variables By..

Jsendzik — Tue, 11 Jun 2013 11:55:56 GMT

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!

Re: Combine / Collapse Binary Variables By..

Tom — Tue, 11 Jun 2013 12:47:14 GMT

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;

Re: Combine / Collapse Binary Variables By..

Jsendzik — Tue, 11 Jun 2013 13:09:05 GMT

Thank you

Re: Combine / Collapse Binary Variables By..

UrvishShah — Tue, 11 Jun 2013 13:30:48 GMT

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