You can perform the reshaping with two TRANSPOSE steps with an intermediate DATA step.
Example:
data have;
input
ID FirstCount FirstSales SecondCount SecondSales ThirdCount ThirdSales FourthCount FourthSales;
informat firstsales secondsales thirdsales fourthsales dollar12.;
format firstsales secondsales thirdsales fourthsales dollar12.;
datalines;
1 23 $156 25 $65,147 89 $215,654 5 $215
2 38 $107,964 17 $123,032 0 $- 520 $138,101
3 0 $- - $- 151 $514,785 0 $-
;
proc transpose data=have out=stage1;
by id;
run;
data stage2;
set stage1;
length type $27;
type = prxchange('s/(count|sales)//i', 1, _name_);
var = tranwrd(_name_, trim(type), '');
run;
proc transpose data=stage2 out=want(drop=_name_);
by id type notsorted;
id var;
var col1;
format sales dollar12.;
run;
Similar process to revert to the original shape
* and back again;
data have;
set want;
run;
proc transpose data=have out=stage1;
by id type notsorted;
run;
data stage2;
set stage1;
_newname_ = cats(type,_name_);
run;
proc transpose data=stage2 out=want(drop=_name_);
by id;
id _newname_;
run;
Note: The OP image shows wide and long not consistent to question.
In general, if you have tall categorical data, a reshaping into the 'wide' shape the task is more a reporting issue than a data transformation issue. Proc TABULATE or REPORT are great for presenting the categorical data in any desired shape.
ods html file='wide.html';
proc tabulate data=tall;
class id type / order=data;
var count sales;
table id='', type='' * (count*sum=''*format=8. sales*sum=''*format=dollar12.) / box='ID';
run;
proc report data=tall;
column id type,(count sales);
define id/group;
define type/across order=data;
run;
ods html close;
... View more
