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!!!!
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.
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.