BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michtka
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
MikeZdeb
Rhodochrosite | Level 12

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

View solution in original post

8 REPLIES 8
MikeZdeb
Rhodochrosite | Level 12

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
Fluorite | Level 6

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.

MikeZdeb
Rhodochrosite | Level 12

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
Fluorite | Level 6

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 Smiley Happy.

V.

michtka
Fluorite | Level 6

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
Fluorite | Level 6

I got it Smiley Happy

proc summary data=new nway missing;

     class treattxt eff_rslt visder;

     var itt;

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

     run;

data_null__
Jade | Level 19

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
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 18125 views
  • 8 likes
  • 3 in conversation