It sounds like you are making a report. If it was, try proc report is able to achieve that goal.
Otherwise, Here is :
data test;
input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26
;
run;
proc transpose data=test out=temp;
by Item Category Subcategory;
run;
data temp;
set temp;
key=upcase(scan(_name_,1,'_'));
run;
proc sql;
select distinct catt('temp(where=(key="',key,'")
rename=(_name_=',key,' col1=',key,'_Fig))')
into : list separated by ' '
from temp;
quit;
data want;
merge &list ;
by Item Category Subcategory;
output;
call missing(of _all_);
drop key;
run;
data want;
set want;
by Item Category Subcategory;
if not first.Subcategory then call missing(Item,Category,Subcategory);
run;
... View more