Transpose table by two variables

Question

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.

Answer 1

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|
--------------------------------------------------------------------

Answer 2

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;