topic Re: retain the format var in proc transpose in SAS Procedures

retain the format var in proc transpose

michtka — Thu, 12 Apr 2012 10:30:40 GMT

retain

Hi Everyone, anyone can tell me why the variable mean dont retain the format when I make proc transpose?

Thanks in advance,

proc means data=newss noprin;
    var vsbasech;
    by vstext visit cpevent treattxt;
    output out=newssmed n=n mean=mean std=std median=median min=min max=max;
    format mean 6.1;
    run;

   proc sort data=newssmed out=newssmeds;
   by visit cpevent;
   run;

proc transpose data=newssmeds out=newssmedst (drop=_label_);
by visit cpevent;
id vstext treattxt;
var n mean std median min max;
run;

Re: retain the format var in proc transpose

MikeZdeb — Thu, 12 Apr 2012 14:01:27 GMT

hi ... one idea on what happens ... if you do this (similar to your job)...

proc summary data=sashelp.class nway;

class age sex;

var height;

output out=stats (drop=_:) n=n mean=mean std=std var=var;

format mean 10.1;

run;

proc transpose data=stats out=tstats name=statistic;

by age;

id sex;

var mean std var;

run;

you are putting all the calculated statistics into two variables, F and M (the two values of the ID variable in transpose) and are combining a formatted MEAN with unformatted STD and VAR (from the VAR statement) ... data set TSTATS ...

Alphabetic List of Variables and Attributes

# Variable Type Len Label

1 Age Num 8

3 F Num 8

4 M Num 8

2 statistic Char 8 NAME OF FORMER VARIABLE

if you change the FORMAT statement in PROC SUMMARY (format all the statistics) ...

format mean std var 10.1;

after the same transpose, you'll see ..

Alphabetic List of Variables and Attributes

# Variable Type Len Format Label

1 Age Num 8

3 F Num 8 F10.1

4 M Num 8 F10.1

2 statistic Char 8 NAME OF FORMER VARIABLE

and ...

Age statistic F M

11 n 1.0 1.0

11 mean 51.3 57.5

11 std . .

11 var . .

11 median 51.3 57.5

12 n 2.0 3.0

12 mean 58.1 60.4

12 std 2.5 3.9

12 var 6.1 15.5

12 median 58.1 59.0

<more>

so, if you format all the statistics in your PROC MEANS, the format should be retained for the variables created by transpose (though you'll have N with a decimal place that's not needed)

format n mean std median min max 6.1;

a suggestion ... since you want to transpose by visit cpevent, how about using a CLASS statement to create a data set that is in the correct order for transpose without having to do that intermediate sort ...

proc summary data=newss nway;
var vsbasech;
class visit cpevent vstext treattxt;
output out=newssmed (drop=_:) n=n mean=mean std=std median=median min=min max=max;
format n mean std median min max 6.1;
run;

proc transpose data=newssmeds out=newssmedst (drop=_label_) name=statistic;
by visit cpevent;
id vstext treattxt;
var n mean std median min max;
run;

Re: retain the format var in proc transpose

michtka — Thu, 12 Apr 2012 15:10:09 GMT

Thanks, it works although you have to force all the statistical values to have the same format , but thanks for your reply.

About by and class, thanks again, it works too ussing class withouth pre- proc sorting unlike I did choosing by....but

There are two differences (I think you will know):

Firstly - BY only works if your dataset is sorted on the specified BY
variables; CLASS works whether or not your dataset is sorted (but if
you want good performance, you should have enough memory to handle all
the classification levels without incurring swapping.)

Secondly - Unlike BY variables, CLASS variables give you automatic
subtotals (unless you specify a keyword such as NWAY or LEVELS). In
your example, CLASS a b will give you an overall total, a subtotal for
each distinct value of a, a subtotal for each distinct value of b, and a
subtotal for each combination of a and b. These different subtotals are
flagged by different values of the _TYPE_ variable which is created
automatically in your output dataset.

Re: retain the format var in proc transpose

MikeZdeb — Thu, 12 Apr 2012 15:20:35 GMT

hi ...

#1 automatic variables and subtotals ... that's why I used (DROP=_:) and NWAY in SUMMARY

#2 sure you have to force all the stats to have the same format ... that's understood since TRANSPOSE deposits all the stats into the same variable and a variable can only have one format

Re: retain the format var in proc transpose

data_null__ — Thu, 12 Apr 2012 16:57:08 GMT

You can have your cake and eat too if you get PROC TRANSPOSE to convert the numeric variables to character...

proc summary data=sashelp.class nway;
   class age sex;
   var height;
   output out=stats (drop=_:)  n=n mean=mean std=std var=var;
   run;


proc transpose data=stats 
      out=tstats(where=(statistic ne 'Sex')) 
      name=statistic;
   by age;
   id sex;
   var n mean std var sex;
   format mean 8.1 n 2. std var 8.2 ;
   run;
proc print;
   run;

Obs Age statistic F M

1 11 n 1 1

2 11 mean 51.3 57.5

3 11 std . .

4 11 var . .

5 12 n 2 3

6 12 mean 58.1 60.4

7 12 std 2.47 3.93

8 12 var 6.13 15.46

9 13 n 2 1

10 13 mean 60.9 62.5

11 13 std 6.22 .

12 13 var 38.72 .

13 14 n 2 2

14 14 mean 63.6 66.3

15 14 std 1.06 3.89

16 14 var 1.13 15.13

17 15 n 2 2

18 15 mean 64.5 66.8

19 15 std 2.83 0.35

20 15 var 8.00 0.13

21 16 n 1

22 16 mean 72.0

23 16 std .

24 16 var .

Re: retain the format var in proc transpose

michtka — Fri, 13 Apr 2012 10:47:06 GMT

Thanks Mikezdeb

totally understand using class, and proc summary nway with the (drop=_:) in the output...it is most better than using two proc sorting and proc means before get the proc transpose.

Thanks a lot .

Re: retain the format var in proc transpose

michtka — Fri, 13 Apr 2012 14:48:38 GMT

Hi MikeZdeb...using your advice with the next dadtaset :

Obs VISit classy pop TREAT

1 Baseline Missing 1 Placebo

                              2    Week 12 LOCF      I            1     Placebo
                              3    Baseline                II           1     Placebo
                              4    Baseline                II           1     Placebo
                              5    Baseline                II           1     Placebo
                              6    Baseline                II           1     Placebo
                              7    Baseline                II           1     Placebo
                              8    Baseline                II           1     Placebo
                              9    Baseline                II           1     Placebo
                             10    Baseline                II           1     Placebo
                             11    Baseline                II           1     Placebo
                             12    Baseline                II           1     Placebo
                             13    Baseline                II           1     Placebo

                             14    Baseline                II           1     Placebo
                             15    Baseline                II           1     Placebo
                             16    Baseline                II           1     Placebo
                             17    Baseline                II           1     Placebo
                             18    Baseline                II           1     Placebo
                             19    Baseline                II           1     Placebo
                             20    Baseline                II           1     Placebo

                             21    Baseline                II           1     Placebo
                             22    Baseline                II           1     Placebo
                             23    Baseline                II           1     Placebo
                             24    Baseline                II           1     Placebo
                             25    Baseline                II           1     Placebo

                             26    Baseline                II           1     Placebo
                             27    Baseline                II           1     Placebo
                             28    Baseline                II           1     Placebo
                             29    Week 12 LOCF      II           1     Placebo

following your advice, i type:

proc summary data=new nway;

class treat classy visit;

var pop;

output out=newsum(drop=_:);

run;

and I obtain:

Obs TREAt class VISit pop

                                    1    Placebo        I        Week 12 LOCF              1
                                    2    Placebo        I        Week 12 LOCF              1
                                    3    Placebo        I        Week 12 LOCF              1
                                    4    Placebo        I        Week 12 LOCF              1
                                    5    Placebo        I        Week 12 LOCF              .
                                    6    Placebo        II       Baseline                 26
                                    7    Placebo        II       Baseline                  1
                                    8    Placebo        II       Baseline                  1
                                    9    Placebo        II       Baseline                  1
                                   10    Placebo        II       Baseline                  0
                                   11    Placebo        II       Week 12 LOCF              1
                                   12    Placebo        II       Week 12 LOCF              1
                                   13    Placebo        II       Week 12 LOCF              1
                                   14    Placebo        II       Week 12 LOCF              1
                                   15    Placebo        II       Week 12 LOCF              .

I was expecting the real summary and aswell the missing value, and it is not...what i am doing wrong here ?

Thanks in advance.

Re: retain the format var in proc transpose

michtka — Fri, 13 Apr 2012 15:10:36 GMT

I got it

proc summary data=new nway missing;

class treattxt eff_rslt visder;

var itt;

output out=newsum(drop=_:) n=n;

run;

Re: retain the format var in proc transpose

michtka — Wed, 25 Apr 2012 10:22:35 GMT

data_null...in termns of using proc means instead of proc summary...is not possible to get the same result...can you give

me some suggestion of the formatting using proc means?...Thanks.