topic Re: Splitting Table in SAS Procedures

Splitting Table

tmcrouse — Thu, 10 May 2012 13:26:03 GMT

I have a table that I am trying to change the layout. Right now it looks like this:

customer ordealcd who orders frequencycount expectedfrequency

1111 1 1111 reorder 1 0.176720721

1111 1 1111 order 3 3.82327929

1111 1 ALL reorder 1838 1838.823729

1111 1 ALL order 39783 39782.17672

2222 2 2222 reorder 1 0.16054635

2222 2 2222 order 5 5.83945365

2222 2 ALL reorder 955 955.83465

2222 2 ALL order 34767 34767.16055

I am trying to get it to look like this and think some sort of proc sql will work but not sure:

customer ordealcd who reorderfreqcount reorderexpect orderfreqcount orderexpect

1111 1 1111 1 0.176720721 3 3.82327929

1111 1 ALL 1838 1838.823729 39783 39782.17672

2222 2 2222 1 0.16054635 5 5.83945365

2222 2 ALL 955 955.83465 34767 37676.16055

Re: Splitting Table

Reeza — Thu, 10 May 2012 14:14:43 GMT

Try some of the methods on this page

Using the Transpose Method

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

Using the Datastep method:

SAS Learning Module: Reshaping data long to wide using the data step

Re: Splitting Table

Ksharp — Thu, 10 May 2012 15:10:27 GMT

EASY.

data have;
input (customer        ordealcd           who                   orders   )     ($)       frequencycount            expectedfrequency ;
cards;
1111                   1               1111                    reorder                           1                           0.176720721
1111                   1               1111                    order                              3                           3.82327929
1111                   1               ALL                     reorder                      1838                           1838.823729
1111                   1               ALL                     order                       39783                            39782.17672
2222                   2             2222                     reorder                            1                            0.16054635
2222                   2             2222                      order                              5                            5.83945365
2222                   2               ALL                     reorder                       955                            955.83465
2222                   2              ALL                      order                       34767                           34767.16055
;
run;
data want(drop=frequencycount  expectedfrequency orders);
 set have;
 by who notsorted;
 retain reorderfrequencycount reorderexpectedfrequency orderfrequencycount orderexpectedfrequency;
 select(orders);
  when('reorder') do;reorderfrequencycount=frequencycount;reorderexpectedfrequency=expectedfrequency;end;
  when('order') do;orderfrequencycount=frequencycount;orderexpectedfrequency=expectedfrequency;end;
  otherwise;
 end;
 if last.who then output;
run;

Ksharp

Re: Splitting Table

Linlin — Thu, 10 May 2012 15:28:12 GMT

Hi Ksharp,

I found out yesterday that it is ok without ";" after "do". Do you know that? Thanks - Linlin

data want(drop=frequencycount expectedfrequency orders);

set have;

by who notsorted;

retain reorderfrequencycount reorderexpectedfrequency orderfrequencycount orderexpectedfrequency;

select(orders);

when('reorder') do reorderfrequencycount=frequencycount;reorderexpectedfrequency=expectedfrequency;end;

when('order') do orderfrequencycount=frequencycount;orderexpectedfrequency=expectedfrequency;end;

otherwise;

end;

if last.who then output;

run;

Re: Splitting Table

tmcrouse — Thu, 10 May 2012 16:25:04 GMT

Is there a way I can do this and within the datastep say do not return anything in the who column that is labeled ALL?

Re: Splitting Table

Linlin — Thu, 10 May 2012 16:50:02 GMT

is this what you want?

data vnt;

data have;

input (customer ordealcd who orders ) ($) frequencycount expectedfrequency ;

cards;

1111 1 1111 reorder 1 0.176720721

1111 1 1111 order 3 3.82327929

1111 1 ALL reorder 1838 1838.823729

1111 1 ALL order 39783 39782.17672

2222 2 2222 reorder 1 0.16054635

2222 2 2222 order 5 5.83945365

2222 2 ALL reorder 955 955.83465

2222 2 ALL order 34767 34767.16055

;

run;

data want(drop=frequencycount expectedfrequency orders);

set have;

by who notsorted;

retain reorderfrequencycount reorderexpectedfrequency orderfrequencycount orderexpectedfrequency;

select(orders);

when('reorder') do;reorderfrequencycount=frequencycount;reorderexpectedfrequency=expectedfrequency;end;

when('order') do;orderfrequencycount=frequencycount;orderexpectedfrequency=expectedfrequency;end;

otherwise;

end;

if last.who and who ne 'ALL' then output;

run;

proc print;run;

                                                         r
                                                         e
                                                         o               o
                                                 r       r               r
                                                 e       d               d
                                                 o       e       o       e
                                                 r       r       r       r
                                                 d       e       d       e
                                                 e       x       e       x
                                                 r       p       r       p
                                                 f       e       f       e
                                                 r       c       r       c
                                                 e       t       e       t
                                                 q       e       q       e
                                                 u       d       u       d
                                                 e       f       e       f
                             c      o            n       r       n       r
                             u      r            c       e       c       e
                             s      d            y       q       y       q
                             t      e            c       u       c       u
                             o      a            o       e       o       e
                       O     m      l     w      u       n       u       n
                       b     e      c     h      n       c       n       c
                       s     r      d     o      t       y       t       y

1 1111 1 1111 1 0.17672 3 3.82328
2 2222 2 2222 1 0.16055 5 5.83945

Re: Splitting Table

tmcrouse — Thu, 10 May 2012 16:53:44 GMT

That is perfect!!!!

Re: Splitting Table

Ksharp — Fri, 11 May 2012 01:53:31 GMT

Linlin,

I think PGStats has already given you right answer.

do reorderfrequencycount=frequencycount;

orderexpectedfrequency=expectedfrequency;

end;

means only loop one time, at this time ,reorderfrequencycount 's initial value is frequencycount.

So this loop contains two statements as :

reorderfrequencycount=frequencycount;

orderexpectedfrequency=expectedfrequency;

It is exactly the same with my code .

Ksharp