Single observation per subject from multiple observation per subject

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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
Super User
Posts: 7,060

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;

View solution in original post


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

Posted in reply to Murray_Court

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
Super User
Posts: 7,976

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
Super User
Posts: 7,976

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 Smiley Happy

Super User
Super User
Posts: 7,976

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
Super User
Posts: 7,060

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 Smiley Happy

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 328 views
  • 10 likes
  • 4 in conversation