BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasecn
Quartz | Level 8

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:

makeHybridSUVSedanSportsTruckWagonnew_var
Acura011100011100
Audi001101001101
........       

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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
Quartz | Level 8

Thanks for the help!

ChrisNZ
Tourmaline | Level 20

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
Quartz | Level 8

Thank you!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 844 views
  • 2 likes
  • 3 in conversation