Counting wide variables and changing to long dataset

[dsbihill]

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

[art297 · Accepted solution]

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;

[SKK]

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;