Transpose Table by Two Variables
Good Day, I Have Technical Issue with Proc Transpose. for Instance My Data Has Structure Like: Data Begin; Input Makname $ Mloname $ Tcode $ Count Percent...
Good day, I have technical issue with proc transpose. For instance my data has structure like:
data begin;
input MAKName $ MLOName $ tcode $ Count Percent;
cards;
ABARTH 124 Miss 5 5.1
ABARTH 124 Hit 94 94.9
FIAT 124 Miss 30 12
FIAT 124 Hit 220 88
;run;
I'd like it to be transposed such that the lines adhere to the format:
MAKName MLOName Count_miss percent_miss Count_hit Percent_hit
ABARTH 124 5 5.0 94 94.9
FIAT 124 30 12 220 88
So I'd like to have two lines compressed to single one. Any permutation of variables or variable names is acceptable.
I've managed to get the hits to transpose, but the second variable gives me issues. Also the naming is a problem, but simple rename could work. in my case.
proc transpose data= Begin out= _test prefix=a_ ;
by makname mloname;
var count ;
idlabel tcode;
run; quit;
Any experienced data manipulator have time to help with this?
Edit below: collage of mine came up with way to do this with 2x transpose:
proc transpose data=begin out=out1;
by MakName MLOName tcode;
var Count Percent;
run;
proc transpose data=out1 out=out2(drop=_NAME_) delimiter=_;
by MakName MLOName;
var Col1;
id _NAME_ tcode;
run;
Neat imho.
2 Answers
There could indeed be a way to do it with one proc transpose but I'm not seeing it.
Alternatevely, you can rather easily do it with two proc transpose and a data step merge:
data begin;
input MAKName $ MLOName $ tcode $ Count Percent;
cards;
ABARTH 124 Miss 5 5.1
ABARTH 124 Hit 94 94.9
FIAT 124 Miss 30 12
FIAT 124 Hit 220 88
;
run;
proc transpose data=Begin out= count_test(drop=_name_) prefix=Count_;
by makname mloname;
var count;
id tcode;
run;
proc transpose data=Begin out= percent_test(drop=_name_) prefix=Percent_;
by makname mloname;
var percent;
id tcode;
run;
data want;
merge count_test percent_test;
by makname mloname;
run;
Note that I replaced your idlabel statement with an id statement in order to create the names for the columns as you want them.
EDIT: the same idea reduced to one proc transpose but still requires merging:
data begin;
input MAKName $ MLOName $ tcode $ Count Percent;
cards;
ABARTH 124 Miss 5 5.1
ABARTH 124 Hit 94 94.9
FIAT 124 Miss 30 12
FIAT 124 Hit 220 88
;
run;
proc transpose data=Begin out=test;
by makname mloname;
var count percent;
id tcode;
run;
data want (drop=_name_);
merge test(where=(_name_='Count') rename=(Miss=Count_miss Hit=Count_hit))
test(where=(_name_='Percent') rename=(Miss=Percent_miss Hit=Percent_hit));
by makname mloname;
run;
I call this form of data shaping multi-pivot. As you learned one traditional approach is the transpose + transpose. Other techniques include:
- transpose + merge (shown by user2877959)
- arrays (for static data configurations)
- hashes (for dynamic data configurations)
- SQL codegen
Regardless of the technique a reshaping of data is often indicative of a reporting requirement. Consider using:
- Proc TABULATE
- Proc REPORT
Here is a tabulation example:
ods listing;
options formchar="|----|+|---+=|-/\<>*";
data have;
input MAKName $ MLOName $ tcode $ Count Percent;
cards;
ABARTH 124 Miss 5 5.1
ABARTH 124 Hit 94 94.9
FIAT 124 Miss 30 12
FIAT 124 Hit 220 88
;run;
proc tabulate data=have;
class MAKName MLOName tcode;
var Count Percent;
table
MAKName * MLOName
,
tcode='' * (Count*max=''*f=8. Percent*max='') / nocellmerge;
run;
ODS Listing output (HTML is way better, but can't be inserted in SO)
--------------------------------------------------------------------
| | Hit | Miss |
| |---------------------+---------------------|
| | Count | Percent | Count | Percent |
|----------------------+--------+------------+--------+------------|
|MAKName |MLOName | | | | |
|----------+-----------| | | | |
|ABARTH |124 | 94| 94.90| 5| 5.10|
|----------+-----------+--------+------------+--------+------------|
|FIAT |124 | 220| 88.00| 30| 12.00|
--------------------------------------------------------------------
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
