Help using Base SAS procedures

Lost in arrays (and reshape?)

Reply
Contributor
Posts: 43

Lost in arrays (and reshape?)

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:

postcodeweek1week2week3week4count_postcode
100NORMALNORMALNORMALNORMAL1
100CAMPAIGNCAMPAIGNNORMALNORMAL2
100FREEFREEFREENORMAL3
100NORMALNORMALNORMALCAMPAIGN4
250NORMALNORMALCAMPAIGNFREE1
250CAMPAIGNCAMPAIGNCAMPAIGNCAMPAIGN2
300NORMALNORMALCAMPAIGNFREE1
300FREEFREECAMPAIGNNORMAL2
300FREEFREEFREEFREE3
400NORMALCAMPAIGNFREEFREE1

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:

postcodeNORMAL_week1CAMPAIGN_week1FREE_week1count_postcode_week1
1002114
2501102
3001023
4001001

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

Super User
Posts: 17,912

Re: Lost in arrays (and reshape?)

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;

Trusted Advisor
Posts: 1,300

Re: Lost in arrays (and reshape?)

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;

Ask a Question
Discussion stats
  • 2 replies
  • 171 views
  • 0 likes
  • 3 in conversation