Single observation per subject from multiple observation per subject

Solved
Occasional Contributor
Posts: 9

Single observation per subject from multiple observation per subject

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

Accepted Solutions
Solution
‎03-20-2014 08:17 AM
Super User
Posts: 8,073

Re: Single observation per subject from multiple observation per subject

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;

All Replies
Contributor
Posts: 45

Re: Single observation per subject from multiple observation per subject

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;

Occasional Contributor
Posts: 9

Re: Single observation per subject from multiple observation per subject

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

Super User
Posts: 9,599

Re: Single observation per subject from multiple observation per subject

Hi,

My response should work regardless of char or numeric.

Occasional Contributor
Posts: 9

Re: Single observation per subject from multiple observation per subject

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

Super User
Posts: 9,599

Re: Single observation per subject from multiple observation per subject

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;

Occasional Contributor
Posts: 9

Re: Single observation per subject from multiple observation per subject

Thank you  .. for all the helpful solutions

Super User
Posts: 9,599

Re: Single observation per subject from multiple observation per subject

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;

Solution
‎03-20-2014 08:17 AM
Super User
Posts: 8,073

Re: Single observation per subject from multiple observation per subject

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;

Occasional Contributor
Posts: 9

Re: Single observation per subject from multiple observation per subject

Thank you .. that was  indeed very helpful

🔒 This topic is solved and locked.