Help using Base SAS procedures

Splitting Table

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Splitting Table

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              


Accepted Solutions
Solution
‎05-10-2012 12:50 PM
Super Contributor
Posts: 1,636

Re: Splitting Table

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

View solution in original post


All Replies
Super User
Posts: 17,826

Re: Splitting Table

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

Super User
Posts: 9,681

Re: Splitting Table

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

Super Contributor
Posts: 1,636

Re: Splitting Table

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;

Super User
Posts: 9,681

Re: Splitting Table

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

Contributor
Posts: 69

Re: Splitting Table

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?

Solution
‎05-10-2012 12:50 PM
Super Contributor
Posts: 1,636

Re: Splitting Table

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

Contributor
Posts: 69

Re: Splitting Table

That is perfect!!!!

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 268 views
  • 3 likes
  • 4 in conversation