BookmarkSubscribeRSS Feed
TMorville
Calcite | Level 5

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

2 REPLIES 2
Reeza
Super User

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;

FriedEgg
SAS Employee

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;

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 714 views
  • 0 likes
  • 3 in conversation