topic Re: Single observation per subject from multiple observation per subject in SAS Programming

Single observation per subject from multiple observation per subject

Ruchita — Thu, 20 Mar 2014 09:52:54 GMT

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

Re: Single observation per subject from multiple observation per subject

Murray_Court — Thu, 20 Mar 2014 09:59:46 GMT

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;

Re: Single observation per subject from multiple observation per subject

RW9 — Thu, 20 Mar 2014 10:06:00 GMT

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;

Re: Single observation per subject from multiple observation per subject

Ruchita — Thu, 20 Mar 2014 10:07:30 GMT

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

Re: Single observation per subject from multiple observation per subject

RW9 — Thu, 20 Mar 2014 10:10:41 GMT

Hi,

My response should work regardless of char or numeric.

Re: Single observation per subject from multiple observation per subject

Ruchita — Thu, 20 Mar 2014 11:33:08 GMT

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

Re: Single observation per subject from multiple observation per subject

RW9 — Thu, 20 Mar 2014 11:54:49 GMT

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;

Re: Single observation per subject from multiple observation per subject

Tom — Thu, 20 Mar 2014 12:17:43 GMT

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;

Re: Single observation per subject from multiple observation per subject

Ruchita — Fri, 21 Mar 2014 12:27:27 GMT

Thank you .. that was indeed very helpful

Re: Single observation per subject from multiple observation per subject

Ruchita — Fri, 21 Mar 2014 12:28:26 GMT

Thank you .. for all the helpful solutions