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:)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.