Help using Base SAS procedures

retain the format var in proc transpose

Accepted Solution Solved
Reply
Super Contributor
Posts: 301
Accepted Solution

retain the format var in proc transpose

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;


Accepted Solutions
Solution
‎04-12-2012 11:20 AM
Valued Guide
Posts: 765

Re: retain the format var in proc transpose

hi ...

#1 automatic variables and subtotals ... that's why I used  (DROP=_Smiley Happy 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

View solution in original post


All Replies
Valued Guide
Posts: 765

Re: retain the format var in proc transpose

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=_Smiley Happy  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=_Smiley Happy 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;

Super Contributor
Posts: 301

Re: retain the format var in proc transpose

Thanks, it works although you have to force all the statistical values to have the same format Smiley Happy, 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.

Solution
‎04-12-2012 11:20 AM
Valued Guide
Posts: 765

Re: retain the format var in proc transpose

hi ...

#1 automatic variables and subtotals ... that's why I used  (DROP=_Smiley Happy 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

Super Contributor
Posts: 301

Re: retain the format var in proc transpose

Thanks Mikezdeb

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

Thanks a lot Smiley Happy.

V.

Super Contributor
Posts: 301

Re: retain the format var in proc transpose

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=_Smiley Happy;

     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.

Super Contributor
Posts: 301

Re: retain the format var in proc transpose

I got it Smiley Happy

proc summary data=new nway missing;

     class treattxt eff_rslt visder;

     var itt;

     output out=newsum(drop=_Smiley Happy n=n;

     run;

Respected Advisor
Posts: 3,799

Re: retain the format var in proc transpose

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                   .

Super Contributor
Posts: 301

Re: retain the format var in proc transpose

Posted in reply to data_null__

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 7638 views
  • 7 likes
  • 3 in conversation