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
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
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
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
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;
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
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?
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
That is perfect!!!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.