DATA Step, Macro, Functions and more

how to convert multiple obs to one ob per subject

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

how to convert multiple obs to one ob per subject

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

 

 

 


Accepted Solutions
Solution
‎05-05-2017 05:20 PM
Occasional Contributor
Posts: 8

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

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!

View solution in original post


All Replies
Solution
‎05-05-2017 05:20 PM
Occasional Contributor
Posts: 8

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

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!

PROC Star
Posts: 7,363

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

[ Edited ]

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

 

Super User
Posts: 5,085

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

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;

Contributor
Posts: 33

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

thanks
Occasional Contributor
Posts: 8

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

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

Contributor
Posts: 33

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

Thanks.Never thought using update its so easySmiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 221 views
  • 3 likes
  • 4 in conversation