topic Re: how to convert multiple obs to one ob per subject in SAS Programming

how to convert multiple obs to one ob per subject

paddyb — Fri, 05 May 2017 20:36:24 GMT

I tried self join but its not working.can anyone tell me how to convert it 1 obs per usubjid

obs usubjid wt wtu temp tempu

1 100 70 kg

2 100 34 c

3 101 77 kg

4 101 30 c

Re: how to convert multiple obs to one ob per subject

Urban_Science — Fri, 05 May 2017 21:05:33 GMT

I know you said you tried a self join, but using proc sql I think it should work. The code would look something like this:

PROC SQL;
  CREATE TABLE WORK.EXAMPLE AS 
     SELECT A.usubjid
        , A.wt
        , A.wtu
        , B.temp
        , B.tempu
      FROM <table> AS A
      INNER JOIN <table> AS B
        ON A.usubjid = B.usubjid
      WHERE A.wt IS NOT NULL
        AND b.TEMP IS NOT NULL
      ;
QUIT;

There might be prettier solutions, but this should get you on your way. Good luck!

Re: how to convert multiple obs to one ob per subject

art297 — Fri, 05 May 2017 21:18:24 GMT

In a datastep, just use the update statement:

data want;
  update have (obs=0) have;
   by usubjid;
run;

Art, CEO, AnalystFinder.com

Note: Modified to incorporate @Astounding's correction

Re: how to convert multiple obs to one ob per subject

Astounding — Fri, 05 May 2017 21:11:54 GMT

Art's suggestion is a good approach (assuming your data set is sorted), but requires a slight modification:

data want;

update have (obs=0) have;

by usubjid;

run;

Re: how to convert multiple obs to one ob per subject

paddyb — Fri, 05 May 2017 21:20:21 GMT

thanks

Re: how to convert multiple obs to one ob per subject

Urban_Science — Fri, 05 May 2017 21:48:36 GMT

That's a cleaver use of update. Thanks for the other method!

Re: how to convert multiple obs to one ob per subject

paddyb — Sat, 06 May 2017 00:36:46 GMT

Thanks.Never thought using update its so easy:)