topic proc transpose 4 variables in SAS Procedures

proc transpose 4 variables

michtka — Thu, 19 Apr 2012 23:19:02 GMT

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?

Thanks in advance.

Re: proc transpose 4 variables

michtka — Fri, 20 Apr 2012 03:06:50 GMT

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;

Re: proc transpose 4 variables

MikeZdeb — Fri, 20 Apr 2012 17:16:32 GMT

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;

Re: proc transpose 4 variables

michtka — Mon, 23 Apr 2012 23:04:25 GMT

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?

Re: proc transpose 4 variables

MikeZdeb — Tue, 24 Apr 2012 03:36:44 GMT

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

Re: proc transpose 4 variables

Ksharp — Tue, 24 Apr 2012 04:20:59 GMT

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;

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

Re: proc transpose 4 variables

MikeZdeb — Tue, 24 Apr 2012 14:12:15 GMT

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;

Re: proc transpose 4 variables

Ksharp — Wed, 25 Apr 2012 02:57:27 GMT

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

Re: proc transpose 4 variables

michtka — Wed, 25 Apr 2012 08:48:27 GMT

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.

Re: proc transpose 4 variables

MikeZdeb — Wed, 25 Apr 2012 12:47:36 GMT

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