proc transpose 4 variables

Solved
Super Contributor
Posts: 301

proc transpose 4 variables

Hi guys, can anyone help me with this:

I got the next dataset new:

data new;

input visit &\$6. treat &\$7. pop :1. phase1 :1. phase2 :1. phase3 :1.;

cards;

week12  placebo    1    1  1 2

week28  placebo    1    2  2 1

week64  placebo    1    1  4 2

week40  placebo    1    2  4 1

week40  drug       1    3  5 1

week12  drug       1    4  3 1

week12  drug       1    3  3 1

week28  placebo    1    3  2 2

week28  placebo    1    4  1 3

week40  placebo    1    4  3 4

week40  placebo    1    5  3 4

week12  drug       1    5  4 4

week64  drug       1    1  3 4

week64  drug       1    3  3 5

week12  drug       1    3  2 3

week12  placebo    1    2  1 4

week64  placebo    1    1  1 2

week28  placebo    1    2  2 1

;

run;

I would like something like that:

week12                              week28                                      week40                       week64

phase1 phase2 phase3        phase1 phase2 phase3      phase1 phase2 phase3       phase1 phase2 phase3

placebo         N                     x           x        x                 x          x         x               x         x            x             x          x            x

mean               x            x       x                 x           x        x               x          x           x             x           x           x

sd                   x            x       x                 x           x         x              x           x          x             x            x          x

drug             N                    x             x       x                x             x         x              x          x           x             x           x           x

mean               x             x       x               x              x         x              x           x          x            x           x           x

sd                   x             x        x             x               x          x            x            x         x              x           x          x

I start trying do something like this:

proc sort data=new out=new2;

by treat visit;

run;

proc means data=new2 noprint;

by treat visit;

output out=new3 n=n mean=mean std=std;

var phase1 phase2 phase3;

run;

but it doesnt work.....any help?

V

Accepted Solutions
Solution
‎04-19-2012 11:06 PM
Super Contributor
Posts: 301

Re: proc transpose 4 variables

data new;

input visit &\$6. treat &\$7. pop :1. phase1 :1. phase2 :1. phase3 :1. dup :1. num :1.;

cards;

week12  placebo    1    1  1 2  1 1

week12  placebo    1    1  1 2  1 2

week12  placebo    1    1  1 2  1 3

week28  placebo    1    2  2 1  2 4

week28  placebo    1    2  2 1  2 5

week28  placebo    1    2  2 1  2 6

week40  placebo    1    3  3 2  3 7

week40  placebo    1    3  3 2  3 8

week40  placebo    1    3  3 2  3 9

week64  placebo    1    4  3 1  4 10

week64  placebo    1    4  3 1  4 11

week64  placebo    1    4  3 1  4 12

week12  drug       2    4  3 1  5 13

week12  drug       2    4  3 1  5 14

week12  drug       2    4  3 1  5 15

week40  drug       2    3  5 1  6 16

week40  drug       2    3  5 1  6 17

week40  drug       2    3  5 1  6 18

week64  drug       2    4  3 4  7 19

week64  drug       2    4  3 4  7 20

week64  drug       2    4  3 4  7 21

week12  placebo    3    3  3 1  8 22

week12  placebo    3    3  3 1  8 23

week12  placebo    3    3  3 1  8 24

week28  placebo    3    4  1 3  9 25

week28  placebo    3    4  1 3  9 26

week28  placebo    3    4  1 3  9 27

week64  placebo    3    1  3 4  10 28

week64  placebo    3    1  3 4  10 29

week64  placebo    3    1  3 4  10 30

week12  drug       4    5  4 4  11 31

week12  drug       4    5  4 4  11 32

week12  drug       4    5  4 4  11 33

week28  drug       4    3  3 5  13 34

week28  drug       4    3  3 5  13 35

week28  drug       4    3  3 5  13 36

week40  drug       4    5  3 4  14 37

week40  drug       4    5  3 4  14 38

week40  drug       4    5  3 4  14 39

week64  drug       4    1  1 2  15 40

week64  drug       4    1  1 2  15 41

week64  drug       4    1  1 2  15 42

week12  placebo    5    2  1 4  16 43

week12  placebo    5    2  1 4  16 44

week12  placebo    5    2  1 4  16 45

week28  placebo    5    2  2 1  17 46

week28  placebo    5    2  2 1  17 47

week28  placebo    5    2  2 1  17 48

;

run;

proc sort data=new out=new2;

by dup;

run;

data new2s;

set new2;

by dup;

if first.dup then output;

run;

proc sort data=new2s out=new2ss;

by treat;

run;

proc transpose data=new2ss out=new2t (rename=(_name_=phasex));

by treat dup visit;

var phase1 phase2 phase3;

run;

data new2cat;

set new2t;

value=cats(visit,phasex);

run;

proc sort data=new2cat out=new2cats;

by treat value;

run;

proc means data=new2cats noprint;

by treat value;

output out=newxxx n=n;

var col1;

run;

proc transpose data=newxxx out=new2catst;

by treat;

id value;

var n;

run;

All Replies
Solution
‎04-19-2012 11:06 PM
Super Contributor
Posts: 301

Re: proc transpose 4 variables

data new;

input visit &\$6. treat &\$7. pop :1. phase1 :1. phase2 :1. phase3 :1. dup :1. num :1.;

cards;

week12  placebo    1    1  1 2  1 1

week12  placebo    1    1  1 2  1 2

week12  placebo    1    1  1 2  1 3

week28  placebo    1    2  2 1  2 4

week28  placebo    1    2  2 1  2 5

week28  placebo    1    2  2 1  2 6

week40  placebo    1    3  3 2  3 7

week40  placebo    1    3  3 2  3 8

week40  placebo    1    3  3 2  3 9

week64  placebo    1    4  3 1  4 10

week64  placebo    1    4  3 1  4 11

week64  placebo    1    4  3 1  4 12

week12  drug       2    4  3 1  5 13

week12  drug       2    4  3 1  5 14

week12  drug       2    4  3 1  5 15

week40  drug       2    3  5 1  6 16

week40  drug       2    3  5 1  6 17

week40  drug       2    3  5 1  6 18

week64  drug       2    4  3 4  7 19

week64  drug       2    4  3 4  7 20

week64  drug       2    4  3 4  7 21

week12  placebo    3    3  3 1  8 22

week12  placebo    3    3  3 1  8 23

week12  placebo    3    3  3 1  8 24

week28  placebo    3    4  1 3  9 25

week28  placebo    3    4  1 3  9 26

week28  placebo    3    4  1 3  9 27

week64  placebo    3    1  3 4  10 28

week64  placebo    3    1  3 4  10 29

week64  placebo    3    1  3 4  10 30

week12  drug       4    5  4 4  11 31

week12  drug       4    5  4 4  11 32

week12  drug       4    5  4 4  11 33

week28  drug       4    3  3 5  13 34

week28  drug       4    3  3 5  13 35

week28  drug       4    3  3 5  13 36

week40  drug       4    5  3 4  14 37

week40  drug       4    5  3 4  14 38

week40  drug       4    5  3 4  14 39

week64  drug       4    1  1 2  15 40

week64  drug       4    1  1 2  15 41

week64  drug       4    1  1 2  15 42

week12  placebo    5    2  1 4  16 43

week12  placebo    5    2  1 4  16 44

week12  placebo    5    2  1 4  16 45

week28  placebo    5    2  2 1  17 46

week28  placebo    5    2  2 1  17 47

week28  placebo    5    2  2 1  17 48

;

run;

proc sort data=new out=new2;

by dup;

run;

data new2s;

set new2;

by dup;

if first.dup then output;

run;

proc sort data=new2s out=new2ss;

by treat;

run;

proc transpose data=new2ss out=new2t (rename=(_name_=phasex));

by treat dup visit;

var phase1 phase2 phase3;

run;

data new2cat;

set new2t;

value=cats(visit,phasex);

run;

proc sort data=new2cat out=new2cats;

by treat value;

run;

proc means data=new2cats noprint;

by treat value;

output out=newxxx n=n;

var col1;

run;

proc transpose data=newxxx out=new2catst;

by treat;

id value;

var n;

run;

Valued Guide
Posts: 765

Re: proc transpose 4 variables

hi ... looks as if you answered your own question but you had to add new variables (DUP and NUM) to the data  ...I find this an odd sequence: post a questions, answer a question , declare the answer correct

anyways ... here's another idea with no DUP or NUM (also ... with LIST INPUT, you don't need those :1. informats for numeric variables and it is more common to use a colon modifier rather than an ampersand to assign a length to a character variable)

data new;

input visit:\$6. treat:\$7. pop phase1-phase3 @@;

cards;

week12  placebo    1    1  1 2  week28  placebo    1    2  2 1

week64  placebo    1    1  4 2  week40  placebo    1    2  4 1

week40  drug       1    3  5 1  week12  drug       1    4  3 1

week12  drug       1    3  3 1  week28  placebo    1    3  2 2

week28  placebo    1    4  1 3  week40  placebo    1    4  3 4

week40  placebo    1    5  3 4  week12  drug       1    5  4 4

week64  drug       1    1  3 4  week64  drug       1    3  3 5

week12  drug       1    3  2 3  week12  placebo    1    2  1 4

week64  placebo    1    1  1 2  week28  placebo    1    2  2 1

;

ods listing close;

ods output moments=ustat (keep=varname treat visit label1 nvalue1 where=(label1 in : ('N' 'Mean' 'Std')));

proc univariate data=new;

var phase: ;

class treat visit;

run;

ods output close;

ods listing;

proc sort data=ustat;

by treat label1;

run;

proc transpose data=ustat out=want (drop=_name_ rename=(label1=stat));

by treat label1;

id visit varname;

var nvalue1;

run;

if not using V9.3 and cannot use two ID variables ...

proc sort data=ustat;

by treat label1;

run;

data ustat;

set ustat;

newvar = catx('_',visit,varname);

run;

proc transpose data=ustat out=want (drop=_name_ rename=(label1=stat));

by treat label1;

id newvar;

var nvalue1;

run;

Super Contributor
Posts: 301

Re: proc transpose 4 variables

Hi, using this ods outputs, you get all the moments of all the phases: phase1, phase2 and phase3.

ods listing close;

ods output moments=ustat (keep=varname treat visit label1 nvalue1 where=(label1 in : ('N' 'Mean' 'Std')));

proc univariate data=new;

var phase: ;

class treat visit;

run;

ods output close;

ods listing;

but if I use this other statement I only get the moments of the phase 1:

proc univariate data=new;

var phase: ;

class treat visit;

output out=ustat2 n=n mean=mean std=std;

run;

Why?

Valued Guide
Posts: 765

Re: proc transpose 4 variables

hi ... there are different "rules" for the output you get using ODS and the output you get using PROC options

from on-line doc for UNIVARIATE ...

"You must provide a VAR statement when you use an OUTPUT statement. To store the same statistic for several analysis variables in the OUT= data set, you specify a list of names in the OUTPUT statement. PROC UNIVARIATE makes a one-to-one correspondence between the order of the analysis variables in the VAR statement and the list of names that follow a statistic keyword. "

so when using just UNIVARIATE statements to get output, change this ...

proc univariate data=new;

var phase: ;

class treat visit;

output out=ustat2 n=n mean=mean std=std;

run;

to this ...

proc univariate data=new noprint;

var phase: ;

class treat visit;

output out=ustat2 n=phase1_n phase2_n phase3_n

mean=phase1_mean phase2_mean phase3_mean

std=phase1_std phase2_std phase3_std

run;

the above behavior is similar to the difference between using this ...

proc freq data=sashelp.class;

tables _all_ / missing noprint out=tables;

run;

and this ...

ods listing close;

ods output onewayfreqs=tables;

proc freq data=sashelp.class;

tables _all_ / missing;

run;

ods output close;

ods listing;

the FREQ proc option only puts a table for the last variable in data set SAHELP.CLASS into data set TABLES while ODS OUTPUT has tables for all variables

Super User
Posts: 10,761

Re: proc transpose 4 variables

Because it  only keep the first variable(i.e. phase1 ) 's mean std. If you need keep them all, should assign a variable name to each of them.

proc univariate data=new;

var phase: ;

class treat visit;

output out=ustat2 n= mean= std=  /autoname;

run;

or

proc univariate data=new;

var phase: ;

class treat visit;

output out=ustat2 n=n1 n2 n3  mean=mean1 mean2 mean3 std=std1 std2 std3;

run;

Ksharp

Valued Guide
Posts: 765

Re: proc transpose 4 variables

hi ... I don't think that AUTONAME has been enabled in UNIVARIATE as it has in SUMMARY and MEANS

so, you have to spell out all the new variable names as in Ksharp's 2nd example ...

proc univariate data=new;

var phase: ;

class treat visit;

output out=ustat2 n=n1 n2 n3  mean=mean1 mean2 mean3 std=std1 std2 std3;

run;

or as posted earlier ...

proc univariate data=new noprint;

var phase: ;

class treat visit;

output out=ustat2 n=phase1_n phase2_n phase3_n

mean=phase1_mean phase2_mean phase3_mean

std=phase1_std phase2_std phase3_std

run;

Super User
Posts: 10,761

Re: proc transpose 4 variables

Mike , you are right, I misunderstood UNIVARITE is MEANS. My bad.

Super Contributor
Posts: 301

Re: proc transpose 4 variables

sorry, I need to try all of this first...but my last question is,

if I use proc means instead of  proc univariate, proc means is following the  same syntax, i.e i can get the same result using proc means? Thanks.

Valued Guide
Posts: 765

Re: proc transpose 4 variables

hi ... yes you can get the same statistics with MEANS as you do with UNIVARIATE

however, if you use UNIVARIATE and ODS OUTPUT to get the statistics into a SAS data set rather than the PROC option (OUTPUT OUT= ...) in either MEANS or UNIVARIATE, the data set orientation is different, for example ...

ods listing close;
ods output moments=ustat1 (keep=varname sex label1 nvalue1 where=(label1 in : ('N' 'Mean' 'Std')));
proc univariate data=sashelp.class;
var height weight;
class sex;
run;
ods output close;
ods listing;

gives you ...

Var
Obs     Name     Sex    Label1                nValue1

1    Height     F     N                    9.000000
2    Height     F     Mean                60.588889
3    Height     F     Std Deviation        5.018328
4    Height     M     N                   10.000000
5    Height     M     Mean                63.910000
6    Height     M     Std Deviation        4.937937
7    Weight     F     N                    9.000000
8    Weight     F     Mean                90.111111
9    Weight     F     Std Deviation       19.383914
10    Weight     M     N                   10.000000
11    Weight     M     Mean               108.950000
12    Weight     M     Std Deviation       22.727186

while the follwoing ...

proc univariate data=sashelp.class noprint;

var height weight;

class sex;

output out=ustat2 n=n_ht n_wt mean=mean_ht mean_wt std=std_ht std_wt;

run;

gives you ...

Obs Sex n_ht n_wt mean_ht mean_wt std_ht std_wt

1 F 9 9 60.5889 90.111 5.01833 19.3839

2 M 10 10 63.9100 108.950 4.93794 22.7272

(similar to what you would get with PROC MEANS output)

the output you want depends on what comes next, what will you do with the data set (to get the output you wanted, I found it easier to use the ODS style output)

one distinct advantage of the ODS output is that you do not have to provide all those variable names when using more tha one varaible in the VAR statement

🔒 This topic is solved and locked.