topic Re: How to change values to variables in New SAS User

How to change values to variables

Melanie621 — Thu, 12 Dec 2019 23:39:49 GMT

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?

Re: How to change values to variables

novinosrin — Thu, 12 Dec 2019 23:59:49 GMT

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;

Re: How to change values to variables

novinosrin — Fri, 13 Dec 2019 00:21:57 GMT


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;

Re: How to change values to variables

Tom — Fri, 13 Dec 2019 00:24:48 GMT

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.

Re: How to change values to variables

Ksharp — Fri, 13 Dec 2019 10:08:11 GMT


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;

Re: How to change values to variables

ballardw — Fri, 13 Dec 2019 19:47:20 GMT

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?