CODEWITHSUNDEEP
sas
photec
photec

Reputation: 81

Consolidating SAS dataset with LAG function

I'm hoping one of you smart folks might be able to help get past a roadblock I'm experiencing with the lag function. I have a dataset with the following structure:

Year   CaseID   PersonID   X  
2003   1        1          3  
2003   1        1          4
2003   2        1          1
2003   2        2          2
2003   2        2          1
2003   3        1          3
2003   3        1          1
2003   3        1          4
2003   3        1          5
2003   3        2          1

What I'm hoping to do is consolidate all X values for each PersonID within each CaseID up three times. For instance, Case 3 Person 1 has four entries, but I just need to consolidate the first three X values onto one row. I'm going to keep researching the lag function a bit, but was, again, hoping that someone might be able to help steer me a little bit in order to get the results below.

Year   CaseID   PersonID   N1   N2   N3
2003   1        1          3    4
2003   2        1          1 
2003   2        2          2    1
2003   3        1          3    1    4  
2003   3        2          1

Thank everyone for looking and help that you might be able to provide :)

Upvotes: 1

Views: 87

Answers (1)

Reeza
Reeza

Reputation: 21274

There's two ways that I see to do this. The first uses a data step with the RETAIN function and BY groups rather than lag. The easiest by far is to transpose and then drop any extraneous variables.

Method 1:

data want;

    set have;
    by year caseid personid;
    retain count N1-N3 ;
    array N(3) N1-N3;

    if first.personID then do;
        count=1;
        call missing(N1, N2, N3);

    end;
    else count+1;

    if count<=3 then N(count)=x;
    if last.personID then output;

    drop count x;

run;

Method 2:

proc transpose data=have out=want2(drop=N4) prefix=N;
    by year caseid personid;
    var X;
run;

Upvotes: 2

Related Questions