CODEWITHSUNDEEP
sas
Sophia
Sophia

Reputation: 99

Combing/Collapsing binary variables into a single row by patient ID in SAS

I am trying to collapse my multiple rows of binary variables into a single row per patient id as depicted in my illustration. Could someone please help me with the SAS code to do this? ThanksIllustration of data have and want

Upvotes: 0

Views: 493

Answers (3)

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

Here is a data step-based solution. Certainly more complex than the above answers, but it does show ways you can use arrays, first. and last. processing, and the retain statement.

Use a retained temporary array to hold the values of 2018-2020 until the last observation of each id group. On the last value of each id, check if each held value is 1 and set each value of the year to a 1 or 0.

data want;
    set have;
    by id;

    array year[3] '2018'n--'2020'n;
    array hold[3] _TEMPORARY_;
    retain hold;

    if(first.id) then call missing(of hold[*]);

    do i = 1 to dim(year);
        if(year[i] = 1) then hold[i] = 1;
    end;

    if(last.id) then do;
        do i = 1 to dim(year);
            year[i] = (hold[i] = 1);
        end;

        output;
    end;

    drop i;

run;

Upvotes: 1

Tom
Tom

Reputation: 51611

If the rule is that to set it to 1 if it is ever 1 then take the MAX. If the rule is to set it to one only if all of them are one then take the MIN.

proc summary data=have nway ;
  by id;
  output out=want max= ;
run;

Upvotes: 3

Reeza
Reeza

Reputation: 21294

Update trick

data want;
update have(obs=0) have;
by id;
run;

Or

proc sql;
create table want as
select ID, max('2018'n) as Y2018, max('2019'n) as Y2019, max('2020'n) as Y2020
from have
group by ID
order by ID;
quit;

Untested because you provided data as images, please post as text, preferably as a data step.

Upvotes: 2

Related Questions