Single observation per subject from multiple observation per subject

[Ruchita]

Dear All,

I need Single observation per subject from multiple observation per subject .

Eg :

id  seq  a   b   c

1    1    5      

1    2        3   

1    3             4

o/p required :

id  seq  a   b   c

1    1    5   3   4

[Murray_Court]

summarize using sql.

proc sql;

create table want as

select id, 1 as seq,

max(a) as a, /* or sum(a) as a, whichever appropriate */

max(b) as b,

max(c) as c

from have

group by id, seq;

quit;

[Ruchita]

My Apologize .. plz consider data in  it to be char form

id  seq  a   b   c

1    1    p      

1    2        q   

1    3             r

o/p required :

id  seq  a   b   c

1    1    p   q   r

[RW9]

Hi,

My response should work regardless of char or numeric.

[Ruchita]

thx for the suggestion..but what if for 3 seq data is available only for one record like  :

id  seq  a   b   c

1    1         

1    2  

1    3              r

Expected o/p :

id  seq  a   b   c

1    1               r

But getting :

id  seq   c

1    1      r

[RW9]

Just add additional when clauses to the inter SQL step per (its dropping them as ID variable is missing):

proc sql;

  create table INTER as

  select  *,

         COALESCE(A,B,C) as VAL,

          case  when A is not null then "A"

                when B is not null then "B"

                when C is not null then "C"

/* here */

                when A is null and B is null and C is null and SEQ=1 then "A"

                when A is null and B is null and C is null and SEQ=2 then "B"

                when A is null and B is null and C is null and SEQ=3 then "C"

                else "" end as MYID

  from    HAVE;

quit;

[Ruchita]

Thank you  .. for all the helpful solutions

[RW9]

Hi,

One way is to coalesce the values into one variable and then transpose them:

data have;
attrib id  seq  a   b   c format=best.;
infile datalines delimiter=',' dsd missover;
input id seq a b c;
datalines;
1,1,5,,      
1,2,,3,   
1,3,,,4
;
run;

proc sql;
  create table INTER as
  select  *,
          COALESCE(A,B,C) as VAL,
          case  when A is not null then "A"
                when B is not null then "B"
                when C is not null then "C"
                else "" end as MYID
  from    HAVE;
quit;
proc transpose data=inter out=want;
  by id;
  var val;
  id myid;
  idlabel myid;
run;

[Tom · Accepted solution]

Use the UPDATE statement to collapse those columns. If you really want the FIRST value of SEQ instead of the LAST then sort with descending SEQ.

data want ;

  update have (obs=0) have ;

  by id ;

run;

[Ruchita]

Thank you .. that was  indeed very helpful