Hi SAS-forum gurus!
I have a problem which might seem straight forward, but i'm simply stuck, as how to proceed.
My data looks (simplisticly) like this:
postcode | week1 | week2 | week3 | week4 | count_postcode |
100 | NORMAL | NORMAL | NORMAL | NORMAL | 1 |
100 | CAMPAIGN | CAMPAIGN | NORMAL | NORMAL | 2 |
100 | FREE | FREE | FREE | NORMAL | 3 |
100 | NORMAL | NORMAL | NORMAL | CAMPAIGN | 4 |
250 | NORMAL | NORMAL | CAMPAIGN | FREE | 1 |
250 | CAMPAIGN | CAMPAIGN | CAMPAIGN | CAMPAIGN | 2 |
300 | NORMAL | NORMAL | CAMPAIGN | FREE | 1 |
300 | FREE | FREE | CAMPAIGN | NORMAL | 2 |
300 | FREE | FREE | FREE | FREE | 3 |
400 | NORMAL | CAMPAIGN | FREE | FREE | 1 |
Except its ~ 80000 rows, and week1 counts to week43, and postcode goes from 0-5000.
What i would ideally like (only looking at week1) as an output was something like:
postcode | NORMAL_week1 | CAMPAIGN_week1 | FREE_week1 | count_postcode_week1 |
100 | 2 | 1 | 1 | 4 |
250 | 1 | 1 | 0 | 2 |
300 | 1 | 0 | 2 | 3 |
400 | 1 | 0 | 0 | 1 |
.. and the same for NORMAL_week2 CAMPAIGN_week2... all the way to week4.
I hope this makes sense.
Im already using arrays, but i simply cant fathom how to get the desired result - so i hope you can help 🙂
Thanks in advance,
Tobias
Ugly....but here it is. I'd prefer and one of the intermediate forms myself:
You'll also have to set the missing to 0.
data have;
input postcode week1 $ week2 $ week3 $ week4 $ count_postcode;
cards;
100 NORMAL NORMAL NORMAL NORMAL 1
100 CAMPAIGN CAMPAIGN NORMAL NORMAL 2
100 FREE FREE FREE NORMAL 3
100 NORMAL NORMAL NORMAL CAMPAIGN 4
250 NORMAL NORMAL CAMPAIGN FREE 1
250 CAMPAIGN CAMPAIGN CAMPAIGN CAMPAIGN 2
300 NORMAL NORMAL CAMPAIGN FREE 1
300 FREE FREE CAMPAIGN NORMAL 2
300 FREE FREE FREE FREE 3
400 NORMAL CAMPAIGN FREE FREE 1
;
run;
data want1;
set have;
array flip(4) week1-week4;
do i=1 to 4;
week=i;
Status=compress(flip(i))||"_Week";
Count=1;
output;
end;
drop count_postcode;
run;
proc means data=want1 noprint;
class postcode week status;
types postcode*week*status postcode*week;
var count;
output out=want2 n=count;
run;
data want3;
length status $30.;
set want2;
if status="" then Status="Count_Postcode_";
run;
proc sort data=want3;
by postcode week;
run;
proc transpose data=want3 out=want4;
by postcode;
id status week;
var count;
run;
proc format;
invalue mytype
'NORMAL' = 1
'CAMPAIGN' = 2
'FREE' = 3 ;
run;
data foo;
input postcode ( week1 week2 week3 week4 ) (:$32.) count_postcode;
cards;
100 NORMAL NORMAL NORMAL NORMAL 1
100 CAMPAIGN CAMPAIGN NORMAL NORMAL 2
100 FREE FREE FREE NORMAL 3
100 NORMAL NORMAL NORMAL CAMPAIGN 4
250 NORMAL NORMAL CAMPAIGN FREE 1
250 CAMPAIGN CAMPAIGN CAMPAIGN CAMPAIGN 2
300 NORMAL NORMAL CAMPAIGN FREE 1
300 FREE FREE CAMPAIGN NORMAL 2
300 FREE FREE FREE FREE 3
400 NORMAL CAMPAIGN FREE FREE 1
;
run;
proc sort data=foo;
by postcode count_postcode;
run;
data bar;
retain postcode;
array n [ 3 /*type*/ , 4 /*week*/ ] NORMAL_week1-NORMAL_week4 CAMPAIGN_week1-CAMPAIGN_week4 FREE_week1-FREE_week4;
call pokelong( repeat( put( 0 , rb8. ) , 11 /*total dim 3x4-1*/ ) , addrlong( n[1,1] ) , 96 /*8x12*/ );
do until(last.postcode);
set foo;
array m [ 4 ] week1-week4;
by postcode;
do _n_ = 1 to dim(m);
n[ input( m[_n_] , mytype. ) , _n_ ]+1;
end;
end;
drop week1-week4;
run;
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.