How to change values to variables

[Melanie621]

I have a data set that has an odd arrangement

It goes

ID  date Measurement Value
1 6/21/06 Weight 170 lbs
1 6/21/06 Height 196 cm
1 6/21/06 Systolic 110 mm/Hg
1 6/21/06 Diastolic 70 mm/Hg

1 7/25/06 Weight 171 lbs
1 7/25/06 Height 196 cm
1 7/25/06 Systolic 109 mm/Hg
1 7/25/06 Diastolic 72 mm/Hg
2 6/21/06 Weight 150 lbs
2 6/21/06 Height 190 cm
2 6/21/06 Systolic 105 mm/Hg
2 6/21/06 Diastolic 75 mm/Hg

I'd like it to be :
ID Date Weight    Height    Systolic     Diastolic     Date 2  Weight   Height   Systolic     Diastolic
1 6/21/06 170 lbs 196 cm 110 mm/Hg 70 mm/Hg  7/25/06 171 lbs 196 cm  109 mm/Hg 72 mm/Hg
2 6/21/06150 lbs 190 cm 105 mm/Hg 75 mm/Hg

Is there a way to do this?

[novinosrin · Accepted solution]

HI @Melanie621 

data have;
input ID  date :mmddyy10. Measurement :$12. Value & $10.;
cards;
1 6/21/06 Weight 170 lbs
1 6/21/06 Height 196 cm
1 6/21/06 Systolic 110 mm/Hg
1 6/21/06 Diastolic 70 mm/Hg
1 7/25/06 Weight 171 lbs
1 7/25/06 Height 196 cm
1 7/25/06 Systolic 109 mm/Hg
1 7/25/06 Diastolic 72 mm/Hg
2 6/21/06 Weight 150 lbs
2 6/21/06 Height 190 cm
2 6/21/06 Systolic 105 mm/Hg
2 6/21/06 Diastolic 75 mm/Hg
;

data temp;
set have;
by id date;
if first.id then n=1;
else if first.date then n+1;
run;

proc transpose data=temp out=want(drop=_name_) delim=_;
by id ;
id  measurement n;
var value;
run;

[Tom]

No need to make the N variable for this example data. Just use ID and DATE in the BY statement.

But you might need it if the same measure name appears more than once on the same date.  For example if took more than one set of BP readings on the same date.

[novinosrin]

data have;
input ID  date :mmddyy10. Measurement :$12. Value & $10.;
format date mmddyy10.;
cards;
1 6/21/06 Weight 170 lbs
1 6/21/06 Height 196 cm
1 6/21/06 Systolic 110 mm/Hg
1 6/21/06 Diastolic 70 mm/Hg
1 7/25/06 Weight 171 lbs
1 7/25/06 Height 196 cm
1 7/25/06 Systolic 109 mm/Hg
1 7/25/06 Diastolic 72 mm/Hg
2 6/21/06 Weight 150 lbs
2 6/21/06 Height 190 cm
2 6/21/06 Systolic 105 mm/Hg
2 6/21/06 Diastolic 75 mm/Hg
;



data temp;
set have;
by id date;
if first.id then n=1;
else if first.date then n+1;
output;
if last.date then do;
 measurement='date';
 value=put(date,mmddyy10.);
 output;
end;
drop date;
run;

proc transpose data=temp out=want(drop=_name_) ;
by id ;
id  measurement n ;
var value ;
run;

[Ksharp]

data have;
input ID  date :mmddyy10. Measurement :$12. Value & $10.;
format date mmddyy10.;
cards;
1 6/21/06 Weight 170 lbs
1 6/21/06 Height 196 cm
1 6/21/06 Systolic 110 mm/Hg
1 6/21/06 Diastolic 70 mm/Hg
1 7/25/06 Weight 171 lbs
1 7/25/06 Height 196 cm
1 7/25/06 Systolic 109 mm/Hg
1 7/25/06 Diastolic 72 mm/Hg
2 6/21/06 Weight 150 lbs
2 6/21/06 Height 190 cm
2 6/21/06 Systolic 105 mm/Hg
2 6/21/06 Diastolic 75 mm/Hg
;

proc transpose data=have out=temp(drop=_name_) ;
by id date;
id  measurement  ;
var value ;
run;
data temp;
 set temp;
 by id;
 if first.id then n=0;
 n+1;
run;
proc transpose data=temp out=temp1(where=(upcase(_name_) not in ('N' 'ID')));
by id n;
var _all_;
run;
proc transpose data=temp1 out=want(drop=_name_) delimiter=_;
by id;
id _name_ n;
var col1;
run;

[ballardw]

Am I allowed to say that I cringe when I see height in metric units and weight in English?

Or is one of the next stages of processing this data to standardize units of measurement?