topic Re: Lost in arrays (and reshape?) in SAS Procedures

Lost in arrays (and reshape?)

TMorville — Wed, 30 Oct 2013 18:02:48 GMT

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

Re: Lost in arrays (and reshape?)

Reeza — Wed, 30 Oct 2013 18:20:38 GMT

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;

Re: Lost in arrays (and reshape?)

FriedEgg — Wed, 30 Oct 2013 21:33:45 GMT

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;

drop week1-week4;

run;