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:)
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.