case statement

[boin]

Hi All,

I have the below table :

postcode	store	sales	rank
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

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	Store1	store2	store3	store1 spend	store2 spend	store3 spend	rest ofspend
3000	storec	storeb	storee	$289,809.00	$ 37,979.00	$ 17,865.00	$ 9,541.00
3162	store3	store1	store4	$ 49,700.00	$ 28,977.00	$ 18,783.00	$ 28,846.00

thanks

[Reeza]

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.

[Steelers_In_DC]

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;

[Ksharp]

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;