topic Re: Summarize Table by Creating two new Columns (Metric Name, Metric Value) in SAS Programming

Summarize Table by Creating two new Columns (Metric Name, Metric Value)

VCucu — Wed, 22 Jul 2020 10:10:44 GMT

Hello,

I have the following table:

Product	Year	Cost	Revenue
A	2020	100	110
A	2020	120	90
B	2020	220	250
B	2020	230	250

I want to create a new table, which replaces the numerical Columns (Cost & Revenue) with two new Columns: text "Metric Name" & Numerical "Metric Value" and summarize them as below;

Product	Year	Metric Name	Metric Value
A	2020	Cost	220
A	2020	Revenue	200
B	2020	Cost	450
B	2020	Revenue	500

Does anybody know how to do this ?

Thank you very much,

Re: Summarize Table by Creating two new Columns (Metric Name, Metric Value)

PeterClemmensen — Wed, 22 Jul 2020 10:13:14 GMT

Is this for reporting purposes or do you want a SAS data set like this?

Re: Summarize Table by Creating two new Columns (Metric Name, Metric Value)

VCucu — Wed, 22 Jul 2020 10:15:36 GMT

i would like a dataset like this. will use it for reporting in a later step with proc report. thank you

Re: Summarize Table by Creating two new Columns (Metric Name, Metric Value)

PeterClemmensen — Wed, 22 Jul 2020 10:23:06 GMT

There you go. Seems easier to report directly though.

data have;
input Product $ Year Cost Revenue;
datalines;
A 2020 100 110
A 2020 120 90
B 2020 220 250
B 2020 230 250
;

data want(keep=Product Year Metric_Name Metric_Value);
   c = 0; r = 0;

   do until (last.Product);
      set have;
      by Product;
      c + Cost;
      r + Revenue;
   end;

   Metric_Name  = "Cost   ";
   Metric_Value = c;
   output;

   Metric_Name  = "Revenue";
   Metric_Value = r;
   output;
run;

Re: Summarize Table by Creating two new Columns (Metric Name, Metric Value)

Kurt_Bremser — Wed, 22 Jul 2020 10:30:19 GMT

If year were character, this would be the most simple code:

proc summary data=have nway;
by product year;
var _numeric_;
output out=sum (drop=_type_ _freq_) sum()=;
run;

proc transpose data=sum out=want (rename=(_name_=metric_name col1=metric_value));
by product year;
var _numeric_;
run;

If, OTOH, you positively know that you have only cost and revenue, then a data step does it it in one operation:

data want;
set have;
by product year;
if first.year
then do;
  _cost = cost;
  _revenue = revenue;
end;
else do;
  _cost + cost;
  _revenue + revenue;
end;
if last.year;
metric_name = 'cost   ';
metric_value = _cost;
output;
metric_name = 'revenue';
metric_value = _revenue;
output;
keep product year metric_name metric_value;
run;