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
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.
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.