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!
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
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
Thanks for the help!
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;
Thank you!
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!
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.
Ready to level-up your skills? Choose your own adventure.