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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.