[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    SAS-L: How to sort, summarize, transpose and create a mutidimensional output crosstab dataset, all i
From:       Roger DeAngelis <rogerjdeangelis () GMAIL ! COM>
Date:       2017-09-28 20:22:28
Message-ID: 5953492567601303.WA.rogerjdeangelisgmail.com () listserv ! uga ! edu
[Download RAW message or body]

SAS-L: How to sort, summarize, transpose and create a mutidimensional output crosstab \
dataset,  all in one proc

  It is the output dataset that is important?

  There are only two procedures in base or stat that can sort, summarize, transpose \
and create a mutidimensional dataset  all in one procedure

      1. Proc corresp (it can only do counts and percents(means if you supply proper
         weight variable). Corresp does support a non integer weight variable.

      2. Proc Report  (most flexible but you have to deal with alphabetically sorted \
_c#_ variables.

      In some cases tabulate can, but it often goes into long and skinny mode for the \
out put dataset.  proc computab can but it is not in base or stat

     WORKING CODE
     ============

          proc report data=have out=HAVXPO
             cols id (name, pay);
             define id / group;
             define name / across;
             define pay  / analysis mean ;

inspired by
https://communities.sas.com/t5/SAS-Procedures/Assistance-with-Proc-Summary/m-p/399446

HAVE
====

   WORK.HAVE total obs=

     Obs     ID    NAME        PAY

       1    111    MARCH      0.20
       2    111    MARCH      0.00
       3    111    APRIL     23.58
       4    111    APRIL      0.14
       5    111    MAY       49.30
       6    111    MAY        0.45
       7    111    JUNE      40.68
       8    111    JUNE       0.05
       9    111    JULY      18.47
      10    111    JULY       0.09
      11    111    AUGUST    20.58
      12    111    AUGUST     0.06
      13    222    MARCH      2.89
      14    222    MARCH      0.00
      15    222    APRIL      3.16
      ...

WANT
====                          MEANS BY MONTH

           APRIL    AUGUST      JULY      JUNE     MARCH       MAY
    ID       PAY       PAY       PAY       PAY       PAY       PAY
   111      11.9      10.3       9.3      20.4       0.1      24.9
   222       1.6       1.8       1.4       3.8       1.4       1.2
   333       0.9       1.6       2.8       0.7       1.1       1.9
   444       3.6       9.5       6.0       3.6       0.3       3.6

*                _               _       _
 _ __ ___   __ _| | _____     __| | __ _| |_ __ _
> '_ ` _ \ / _` | |/ / _ \   / _` |/ _` | __/ _` |
> > > > > > (_| |   <  __/  | (_| | (_| | || (_| |
> _| |_| |_|\__,_|_|\_\___|   \__,_|\__,_|\__\__,_|

;


data have;
 input ID NAME$ PAY;
cards4;
 111 MARCH 0.2049
 111 MARCH 0.0012
 111 APRIL 23.5859
 111 APRIL 0.1447
 111 MAY 49.3062
 111 MAY 0.4578
 111 JUNE 40.6821
 111 JUNE 0.0510
 111 JULY 18.4782
 111 JULY 0.0911
 111 AUGUST 20.5891
 111 AUGUST 0.0622
 222 MARCH 2.8901
 222 MARCH 0.0010
 222 APRIL 3.1668
 222 APRIL 0.0069
 222 MAY 2.3671
 222 MAY 0.0234
 222 JUNE 7.5548
 222 JUNE 0.0087
 222 JULY 2.8367
 222 JULY 0.0156
 222 AUGUST 3.6810
 222 AUGUST 0.0033
 333 MARCH 2.2455
 333 MARCH 0.0139
 333 APRIL 1.8309
 333 APRIL 0.0070
 333 MAY 3.8736
 333 MAY 0.0257
 333 JUNE 1.4406
 333 JUNE 0.0050
 333 JULY 5.4958
 333 JULY 0.0433
 333 AUGUST 3.0906
 333 AUGUST 0.0119
 444 MARCH 0.6419
 444 MARCH 0.0032
 444 APRIL 7.2413
;;;;
run;quit;


*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
> ___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

* sort, summarize and transpose in one procedure;

options ls=171;
proc report data=have
      out=havxpo(rename=(
          _C2_ = APRIL
          _C3_ = AUGUST
          _C4_ = JULY
          _C5_ = JUNE
          _C6_ = MARCH
          _C7_ = MAY));
 cols id (name, pay);
 define id / group;
 define name / across;
 define pay  / analysis mean format=5.1 width=8;
run;quit;


[prev in list] [next in list] [prev in thread] [next in thread]