DATA Step, Macro, Functions and more

Combine / Collapse Binary Variables By..

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

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!


Accepted Solutions
Solution
‎06-11-2013 08:47 AM
Super User
Super User
Posts: 7,076

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;

View solution in original post


All Replies
Solution
‎06-11-2013 08:47 AM
Super User
Super User
Posts: 7,076

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;

Contributor
Posts: 35

Re: Combine / Collapse Binary Variables By..

Thank you

Regular Contributor
Posts: 195

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;

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

   end;

run;

-Urvish

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 395 views
  • 1 like
  • 3 in conversation