topic Re: Data Transformation SAS Help: from Rows to Columns in SAS Programming

Data Transformation SAS Help: from Rows to Columns

ecc14 — Thu, 11 Oct 2018 20:33:55 GMT

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

Re: Data Transformation SAS Help: from Rows to Columns

Astounding — Thu, 11 Oct 2018 20:40:22 GMT

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.

Re: Data Transformation SAS Help: from Rows to Columns

novinosrin — Thu, 11 Oct 2018 20:54:35 GMT

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;

Re: Data Transformation SAS Help: from Rows to Columns

ecc14 — Thu, 11 Oct 2018 21:04:21 GMT

Thank you so much Astounding! This worked for me.

Re: Data Transformation SAS Help: from Rows to Columns

Andygray — Thu, 11 Oct 2018 21:12:16 GMT

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.

Re: Data Transformation SAS Help: from Rows to Columns

Ksharp — Fri, 12 Oct 2018 12:56:40 GMT

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;

Re: Data Transformation SAS Help: from Rows to Columns

Ksharp — Fri, 12 Oct 2018 12:57:42 GMT

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;

Re: Data Transformation SAS Help: from Rows to Columns

ecc14 — Fri, 12 Oct 2018 19:28:12 GMT

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;')

Re: Data Transformation SAS Help: from Rows to Columns

Ksharp — Sat, 13 Oct 2018 10:21:08 GMT

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;