retain the format var in proc transpose

[michtka]

retain

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

Thanks in advance,

V

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;

[MikeZdeb]

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;

[michtka]

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.

[MikeZdeb · Accepted solution]

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

[michtka]

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 .

V.

[michtka]

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.

[michtka]

I got it

proc summary data=new nway missing;

     class treattxt eff_rslt visder;

     var itt;

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

     run;

[data_null__]

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                   .

[michtka]

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.