reshape table by values of a variable

[sasecn]

Hello:

Suppose I have a table from sashelp.cars, call it "have" which can be obtained from the code attached. I want a result table that has variables: make, Hybrid, SUV, Sedan, Sports, Truck, Wagon, new_var. The 2nd to the 7th vars are the available values of the "type" variable in table "have". They will have numeric values of 0 or 1 to indicate the "types" for each "make" e.g the want table will be something like:

make	Hybrid	SUV	Sedan	Sports	Truck	Wagon	new_var
Acura	0	1	1	1	0	0	011100
Audi	0	0	1	1	0	1	001101
........

The new_var will be char type of variable that has the commination of the types. If we have hundreds of values for "type", what will be the best way to create the new_var? i.e. how to create it without using cat function by list all the names of those vars?

proc sql;
create table have (drop=count) as
select make, type, count(*) as count
from sashelp.cars
group by make, type
;
quit;

 Thanks for any help!

[Tom · Accepted solution]

PROC SUMMARY and PROC TRANSPOSE should do it.

proc summary data=sashelp.cars nway ;
  by make ;
  class type;
  output out=tall;
run;

proc transpose data=tall out=want(drop=_name_);
  by make ;
  id type ;
  var _type_;
run;

If you want to replace the missing with zeros then add a data step.

You might need to know the number of distinct TYPE values to set the length of the character variable.

data want;
  set want;
  array vars _numeric_;
  do _n_=1 to dim(vars);
    vars[_n_] = sum(vars[_n_],0);
  end;
  length new_var $10;
  new_var = cats(of vars[*]);
run; 

Resutls:

Obs    Make             SUV    Sedan    Sports    Wagon    Truck    Hybrid    new_var

  1    Acura             1       1         1        0        0         0      111000
  2    Audi              0       1         1        1        0         0      011100
  3    BMW               1       1         1        1        0         0      111100
  4    Buick             1       1         0        0        0         0      110000
  5    Cadillac          1       1         1        0        1         0      111010
  6    Chevrolet         1       1         1        1        1         0      111110
  7    Chrysler          0       1         1        1        0         0      011100
  8    Dodge             1       1         1        0        1         0      111010
  9    Ford              1       1         1        1        1         0      111110
 10    GMC               1       1         0        0        1         0      110010
 11    Honda             1       1         1        0        0         1      111001
 12    Hummer            1       0         0        0        0         0      100000
 13    Hyundai           1       1         1        0        0         0      111000
 14    Infiniti          0       1         0        1        0         0      010100
 15    Isuzu             1       0         0        0        0         0      100000
 16    Jaguar            0       1         1        0        0         0      011000
 17    Jeep              1       0         0        0        0         0      100000
 18    Kia               1       1         0        1        0         0      110100
 19    Land Rover        1       0         0        0        0         0      100000
 20    Lexus             1       1         1        1        0         0      111100
 21    Lincoln           1       1         0        0        0         0      110000
 22    MINI              0       1         0        0        0         0      010000
 23    Mazda             1       1         1        0        1         0      111010
 24    Mercedes-Benz     1       1         1        1        0         0      111100
 25    Mercury           1       1         0        1        0         0      110100
 26    Mitsubishi        1       1         1        1        0         0      111100
 27    Nissan            1       1         1        1        1         0      111110
 28    Oldsmobile        0       1         0        0        0         0      010000
 29    Pontiac           1       1         1        1        0         0      111100
 30    Porsche           1       0         1        0        0         0      101000
 31    Saab              0       1         0        1        0         0      010100
 32    Saturn            1       1         0        1        0         0      110100
 33    Scion             0       1         0        1        0         0      010100
 34    Subaru            0       1         1        1        1         0      011110
 35    Suzuki            1       1         0        1        0         0      110100
 36    Toyota            1       1         1        1        1         1      111111
 37    Volkswagen        1       1         0        1        0         0      110100
 38    Volvo             1       1         0        1        0         0      110100

[sasecn]

Thanks for the help!

[ChrisNZ]

Another way:

proc sql;
  create table HAVE as
  select c.MAKE, t.TYPE, sum(c.TYPE = t.TYPE) as COUNT
  from (select unique TYPE from SASHELP.CARS)        t
       cross join 
       (select unique TYPE, MAKE from SASHELP.CARS)  c
  group by c.MAKE, t.TYPE 
  order by c.MAKE ;
quit;

proc transpose data=HAVE out=TRAN(drop=_NAME_);
  by MAKE ;
  id TYPE ;
  var COUNT;
run;

data WANT;  
  set TRAN;
  length NEW_VAR $8;
  NEW_VAR = cats(of _NUMERIC_);
run; 

[sasecn]

Thank you!