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
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!
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!
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
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;
That's a cleaver use of update. Thanks for the other method!
Thanks.Never thought using update its so easy:)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.