Solved
Contributor
Posts: 55

# 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
Contributor
Posts: 31

## 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!

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

## 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!

Super User
Posts: 8,220

## 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: 6,939

## 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: 55

thanks
Contributor
Posts: 31

## 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: 55

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

Thanks.Never thought using update its so easy

🔒 This topic is solved and locked.