topic Re: Transpose Help in New SAS User

Transpose Help

Jane_V — Thu, 21 Feb 2019 09:14:49 GMT

Hello Everyone,

I am trying to transpose this dataset but don't know how to transpose weightu, tempu and hru under one variable name "UNIT" (these are the units for weight, temp and hr respectively) along with the respective observation. Please help!

data VS;
input usubjid weight weightu $ temp tempu $ hr hru $ visit $ dd mm yy;
datalines;
100 102 KG 38 C 70 BEATS/MIN WEEK1 01 01 2000
100 225 KG 97 F 71 BEATS/MIN WEEK2 14 01 2000
100 112 KG 38 C 72 BEATS/MIN WEEK3 28 01 2000
100 102 KG 38 C 72 BEATS/MIN WEEK4 07 02 2000
101 220 KG 98.6 C 75 BEATS/MIN WEEK1 01 01 2000
101 102 KG 38 C 72 BEATS/MIN WEEK2 14 01 2000
101 104 KG 38 C 73 BEATS/MIN WEEK3 28 01 2000
;
run;
proc sort data=vs out=sorted_vs;
by usubjid visit dd mm yy;
run;

proc transpose data=sorted_vs out=transp_vs name=TESTCD prefix=results;
by usubjid visit dd mm yy;
var weight temp hr;
run;

Re: Transpose Help

Jagadishkatam — Thu, 21 Feb 2019 09:45:26 GMT

you need to add two more steps to your code to get the units

data VS;
input usubjid weight weightu $ temp tempu $ hr hru $ visit $ dd mm yy;
datalines;
100 102 KG 38 C 70 BEATS/MIN WEEK1 01 01 2000 
100 225 KG 97 F 71 BEATS/MIN WEEK2 14 01 2000 
100 112 KG 38 C 72 BEATS/MIN WEEK3 28 01 2000 
100 102 KG 38 C 72 BEATS/MIN WEEK4 07 02 2000 
101 220 KG 98.6 C 75 BEATS/MIN WEEK1 01 01 2000 
101 102 KG 38 C 72 BEATS/MIN WEEK2 14 01 2000 
101 104 KG 38 C 73 BEATS/MIN WEEK3 28 01 2000 
;
run;

proc sort data=vs out=sorted_vs;
by usubjid visit dd mm yy;
run;

proc transpose data=sorted_vs out=transp_vs name=TESTCD prefix=results;
by usubjid visit dd mm yy;
var weight temp hr;
run;

proc transpose data=sorted_vs out=transp_vs2 name=TESTCD prefix=units;
by usubjid visit dd mm yy;
var weightu tempu hru;
run;

data all;
merge transp_vs transp_vs2;
by usubjid visit dd mm yy;
run;

Re: Transpose Help

Kurt_Bremser — Thu, 21 Feb 2019 09:50:33 GMT

Or you use a custom data step:

data VS;
input usubjid weight weightu $ temp tempu $ hr hru $ visit $ dd mm yy;
datalines;
100 102 KG 38 C 70 BEATS/MIN WEEK1 01 01 2000 
100 225 KG 97 F 71 BEATS/MIN WEEK2 14 01 2000 
100 112 KG 38 C 72 BEATS/MIN WEEK3 28 01 2000 
100 102 KG 38 C 72 BEATS/MIN WEEK4 07 02 2000 
101 220 KG 98.6 C 75 BEATS/MIN WEEK1 01 01 2000 
101 102 KG 38 C 72 BEATS/MIN WEEK2 14 01 2000 
101 104 KG 38 C 73 BEATS/MIN WEEK3 28 01 2000 
;
run;

data want;
set vs;
array units {3} weightu hru tempu;
array values {3} weight hr temp;
do i = 1 to 3;
  unit = units{i};
  value = values{i};
  output;
end;
keep usubjid unit value visit dd mm yy;
run;

Re: Transpose Help

Jagadishkatam — Thu, 21 Feb 2019 09:53:38 GMT

another approach would be with arrays which is the simplest

data VS;
input usubjid weight weightu $ temp tempu $ hr hru $ visit $ dd mm yy;
datalines;
100 102 KG 38 C 70 BEATS/MIN WEEK1 01 01 2000 
100 225 KG 97 F 71 BEATS/MIN WEEK2 14 01 2000 
100 112 KG 38 C 72 BEATS/MIN WEEK3 28 01 2000 
100 102 KG 38 C 72 BEATS/MIN WEEK4 07 02 2000 
101 220 KG 98.6 C 75 BEATS/MIN WEEK1 01 01 2000 
101 102 KG 38 C 72 BEATS/MIN WEEK2 14 01 2000 
101 104 KG 38 C 73 BEATS/MIN WEEK3 28 01 2000 
;
run;

proc sort data=vs out=sorted_vs;
by usubjid visit dd mm yy;
run;

data want;
set vs;
by usubjid visit dd mm yy;
array testcds(3) weight temp hr;
array testu(3)$ weightu tempu hru;
array testlbl(3)$ ('weight' 'temp' 'hr');
do i = 1 to 3;
if first.yy then testcd=testlbl(i);
if first.yy then result=testcds(i);
if first.yy then units=testu(i);
output;
end;
drop testlbl: i;
run;

Re: Transpose Help

RW9 — Thu, 21 Feb 2019 10:02:56 GMT

You would need a few transposes to do it like that. Easier with arrays:

data vs;
  input usubjid weight weightu $ temp tempu $ hr hru $ visit $ dd mm yy;
datalines;
100 102 KG 38 C 70 BEATS/MIN WEEK1 01 01 2000 
100 225 KG 97 F 71 BEATS/MIN WEEK2 14 01 2000 
100 112 KG 38 C 72 BEATS/MIN WEEK3 28 01 2000 
100 102 KG 38 C 72 BEATS/MIN WEEK4 07 02 2000 
101 220 KG 98.6 C 75 BEATS/MIN WEEK1 01 01 2000 
101 102 KG 38 C 72 BEATS/MIN WEEK2 14 01 2000 
101 104 KG 38 C 73 BEATS/MIN WEEK3 28 01 2000 
;
run;

proc sort data=vs out=sorted_vs;
  by usubjid visit dd mm yy;
run;

data want (keep=usubjid visit dd mm yy paramcd aval paru);
  set sorted_vs;
  array test{3} weight temp hr;
  array unit{3} weightu tempu hru;
  do i=1 to 3;
    paramcd=vname(test{i});
    aval=test{i};
    paru=unit{i};
    output;
  end;
run;

Re: Transpose Help

Jane_V — Fri, 22 Feb 2019 01:09:16 GMT

Thank you everyone!

At this novice stage, all the solutions (with/ without proc transpose) are really helpful to learn SAS.

Best!