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

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              

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

7 REPLIES 7
Reeza
Super User

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

Ksharp
Super User

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

Linlin
Lapis Lazuli | Level 10

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;

Ksharp
Super User

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

tmcrouse
Calcite | Level 5

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?

Linlin
Lapis Lazuli | Level 10

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

tmcrouse
Calcite | Level 5

That is perfect!!!!

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
  • 7 replies
  • 892 views
  • 3 likes
  • 4 in conversation