Solved
Contributor
Posts: 69

# 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

All Replies
Super User
Posts: 23,712

## Re: Splitting Table

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: 10,778

## 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: 10,778

## Re: Splitting Table

Linlin,

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 and locked.