Need to reformat a data set using SAS

[lbirming]

Hello,

I have a dataset that looks something like this;

Patient Visit# Sex Blood Urine
1000 01 M
1000 02 4.5 5.2
1000 03 4.2 5.1
1001 01 F
1001 02 3.5 6.2
1001 03 3.2 5.3
...

I would like to have it look like this:

Patient Sex Blood01 Urine01 Blood02 Urine02
1000 M 4.5 5.2 4.2 5.1
1001 F 3.5 6.2 3.2 5.3

This difference here is that I want to see one row per patient (with their blood/ urine results by visit code across the columns).

I'm only somewhat familiar with SAS macros-- and I think that's what I need to solve this problem, but I'm not sure where to start. Any help would be greatly appreciated-- or event just some pointers to get me going in the write direction.

Thank you!

 

[beccy123]

Hey,

There may be an easier way to do it, but one method is using proc transpose. You'd have to play around with the sex field first - i'd probably remove that row, sort out the rest and then add it back in.

But if you assume you start off with the following dataset called "test":

Patient VisitNum Blood Urine
1000 1 4.5 5.2
1000 2 4.2 5.1
1001 1 3.5 6.2
1001 2 3.2 5.3


You can run the following code:

data test;
set test;
bloodnum=compress("Blood"||visitnum);
urinenum=compress("Urine"||visitnum);
run;
proc transpose data=test out=testblood(drop=_name_ _label_);
by patient;
id bloodnum;
var blood;
run;
proc transpose data=test out=testurine(drop=_name_ _label_);
by patient;
id urinenum;
var urine;
run;

proc sql;
create table final
as select a.*,b.*
from testblood a
left join testurine b
on a.patient=b.patient;
quit;

Not sure if that helps?