Hello all i am still very new to sas and working to change some data from a wide to long dataset. I have tried a number of things from Arrays to proc transpose but have not been able to get the output im looking for. It is quite likely that i failed to use one of these procedures correctly. Any help in getting me on the right tool or procedure would be greatly appreciated.
I have input data set that looks like this.
Customer braketype horsepower truck_type hvactype
A CCB2 4000 hiad side
A Knorr 3000 steerable floor
A Knorr 4000 steerable floor
A CCB2 4000 hiad side
A CCB2 3000 hiad side
A CCB2 4000 hiad side
B Knorr 2000 hiad floor
B Knorr 2000 FB side
B CCB2 4000 FB floor
B CCB2 4000 hiad floor
B CCB2 2000 hiad side
B CCB2 2000 hiad side
I need an output with counts of each of these in a long data format like this
Customer Config Type count
A braketype CCB2 4
A braketype Knorr 2
A horsepower 4000 4
A horsepower 3000 2
A truck_type hiad 4
A truck_type steerable 2
A hvac_type side 4
A hvac_type side 2
B braketype Knorr 2
B braketype CCB2 4
B horsepower 2000 4
B horsepower 4000 2
B Truck_type FB 2
B Truck_type Hiad 4
B hvac_type floor 3
B hvac_type side 3
Here is one way:
data need (keep=Customer Config Type);
set have;
length Config Type $10;
Config='braketype';
Type=braketype;
output;
Config='horsepower';
Type=put(horsepower,4.);
output;
Config='truck_type';
Type=truck_type;
output;
Config='hvactype';
Type=hvactype;
output;
run;
proc sql;
select distinct Customer, Config, Type, count(Type) as count
from need
group by Customer, Config, Type
;
quit;
Here is one way:
data need (keep=Customer Config Type);
set have;
length Config Type $10;
Config='braketype';
Type=braketype;
output;
Config='horsepower';
Type=put(horsepower,4.);
output;
Config='truck_type';
Type=truck_type;
output;
Config='hvactype';
Type=hvactype;
output;
run;
proc sql;
select distinct Customer, Config, Type, count(Type) as count
from need
group by Customer, Config, Type
;
quit;
Hi hers's another method, with a little peek from Arthur code:
proc transpose data = have out=w1;
by Customer;
var braketype horsepower truck_type hvactype;
run;
proc transpose data=w1 out=w2(rename=(_name_=config col1=type));
by Customer _name_ notsorted;
var col1-col6;
run;
proc sql;
create table want as
select distinct Customer, Config " ", Type " ", count(Type) as count
from w2
group by Customer, Config, Type
;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.