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
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;
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;
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
Hi,
My response should work regardless of char or numeric.
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
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;
Thank you .. for all the helpful solutions
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;
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;
Thank you .. that was indeed very helpful
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.