how to convert multiple obs to one ob per subject

[paddyb]

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

[Urban_Science · Accepted solution]

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!

[art297]

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

[Astounding]

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;

[paddyb]

thanks

[Urban_Science]

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

[paddyb]

Thanks.Never thought using update its so easy:)