Data Transformation SAS Help: from Rows to Columns

[ecc14]

Hi, 

I am trying to transform how my data is laid out. The data that I am starting with is formatted as such: 

Product	Metric	Actual	Forecast	Total
A	Sales	10	10	20
A	Profit	5	5	10
B	Sales	20	20	40
B	Profit	15	15	30

I am trying to transform my data to look like this:

Product	Sales Actual	Sales Forecast	Sales Total	Profit Actual	Profit Forecast	Profit Total
A	10	10	20	5	5	10
B	20	20	40	15	15	30

I am newer to SAS, but have had success with transposing data in the past though i have not been able to get it to work with this dataset. 

Thank you in advance for the help,

Emily

[Astounding · Accepted solution]

Here's one way:

data want;

merge have (where=(metric='Sales') rename=(actual=Sales_Actual forecast=Sales_Forecast total=Sales_Total))

have (where=(metric='Profit') rename=(actual=Profit_Actual forecast=Profit_Forecast total=Profit_Total));

by product;

drop metric;

run;

It assumes your data set is already sorted by PRODUCT.

[ecc14]

Thank you so much Astounding! This worked for me.

[novinosrin]

data have;
input Product $	Metric $	Actual	Forecast	Total;
cards;
A	Sales	10	10	20
A	Profit	5	5	10
B	Sales	20	20	40
B	Profit	15	15	30
;

proc transpose data=have out=_have;
by product metric notsorted;
var Actual	Forecast	Total;
run;

proc transpose data=_have out=want;
by product;
var col1;
id metric _name_;
run;

[Andygray]

I don't think astounding solution is a proper automated solution. In my opinion that's hard coding and will not scale if the number of metric changes. 

[Ksharp]

OK. Merge Skill come to rescue. proposed by me,Matt,Arthur.T:

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

data have;
input Product $	Metric $	Actual	Forecast	Total;
cards;
A	Sales	10	10	20
A	Profit	5	5	10
B	Sales	20	20	40
B	Profit	15	15	30
;

proc sort data=have(keep=Metric) out=key nodupkey;
by Metric;
run;
data _null_;
 set key end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('have(where=(Metric="',Metric,'") 
 rename=(Actual=',Metric,'_Actual Forecast=',Metric,'_Forecast Total=',Metric,'_Total))'));
 if last then call execute(';by Product;drop Metric;run;');
 run;

[Ksharp]

OK. Merge Skill come to rescue. proposed by me,Matt,Arthur.T:

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

data have;
input Product $	Metric $	Actual	Forecast	Total;
cards;
A	Sales	10	10	20
A	Profit	5	5	10
B	Sales	20	20	40
B	Profit	15	15	30
;

proc sort data=have(keep=Metric) out=key nodupkey;
by Metric;
run;
data _null_;
 set key end=last;
 if _n_=1 then call execute('data want;merge ');
 call execute(catt('have(where=(Metric="',Metric,'") 
 rename=(Actual=',Metric,'_Actual Forecast=',Metric,'_Forecast Total=',Metric,'_Total))'));
 if last then call execute(';by Product;drop Metric;run;');
 run;

[ecc14]

Do you mind walking me through what you are doing here? I'm not sure I am following:

call execute(catt('have(where=(Metric="',Metric,'") 
 rename=(Actual=',Metric,'_Actual Forecast=',Metric,'_Forecast Total=',Metric,'_Total))'));
 if last then call execute(';by Product;drop Metric;run;')

[Ksharp]

This produce the Astounding's code .



data want;

merge have (where=(metric='Sales') rename=(actual=Sales_Actual forecast=Sales_Forecast total=Sales_Total))

have (where=(metric='Profit') rename=(actual=Profit_Actual forecast=Profit_Forecast total=Profit_Total));

by product;

drop metric;

run;