case statement

Reply
Contributor
Posts: 44

case statement

Hi All,

I have the below table :

postcode
storesalesrank
3000storec2898091
3000storeb379792
3000storee178653
3000Restof95414
3162store3497001
3162store1289772
3162store4187833
3162Restof28846

And i am trying to get the output as below:

I have used Case statement to get the below output however no luck, i have more than 1000 postcodes in this data-set, so any ideas would be great..


postcode
Store1store2store3store1 spendstore2 spendstore3 spendrest ofspend
3000storecstorebstoree $289,809.00 $ 37,979.00 $ 17,865.00 $ 9,541.00
3162store3store1store4 $ 49,700.00 $ 28,977.00 $ 18,783.00 $ 28,846.00

thanks

Super User
Posts: 19,878

Re: case statement

You're trying to transpose your data from long to wide. Two common methods are via a data step or a proc transpose.

Examples of the following are here:

SAS Learning Module: Reshaping wide to long using a data step

SAS Learning Module: How to reshape data wide to long using proc transpose

I'd recommend the data step in this particular case, especially if you have exactly 4 of each.

If you were trying to do this via sql I would do it via merging subqueries rather than CASE statements.

Valued Guide
Posts: 860

Re: case statement

Here is the solution that I came up with, not sure that this will work but I wouldn't have store3 in store1 column, store1 in store2 column and store4 in store3 column:

data have;

infile cards dsd;

input postcode $ store $  sales rank;

cards;

3000,storec,289809,1

3000,storeb,37979,2

3000,storee,17865,3

3000,Restof,9541,4

3162,store3,49700,1

3162,store1,28977,2

3162,store4,18783,3

3162,Restof,28846,4

;

run;

proc sort data=have;by postcode store;

proc transpose data=have(drop=rank) out=tran_have(drop=_name_);by postcode store;

proc transpose data=tran_have out=prep(drop=_name_);by postcode;id store;var col1;

Super User
Posts: 10,048

Re: case statement

The simplest way is using proc means . Otherwise consider speed , you could use MERGE skill.

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;

infile cards dsd;

input postcode $ store $  sales rank;

cards;

3000,storec,289809,1

3000,storeb,37979,2

3000,storee,17865,3

3000,Restof,9541,4

3162,store3,49700,1

3162,store1,28977,2

3162,store4,18783,3

3162,Restof,28846,4

;

run;

proc sql;

select max(n) into : n

  from (select count(*) as n from have group by postcode);

quit;

proc summary data=have ;

by postcode;

output out=want idgroup(out[&n] (store  sales rank)=);

run;

Ask a Question
Discussion stats
  • 3 replies
  • 263 views
  • 0 likes
  • 4 in conversation