Counting wide variables and changing to long dataset

dsbihill — Tue, 29 Apr 2014 03:13:26 GMT

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

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

Re: Counting wide variables and changing to long dataset

art297 — Tue, 29 Apr 2014 04:01:39 GMT

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;

Re: Counting wide variables and changing to long dataset

SKK — Tue, 29 Apr 2014 04:56:17 GMT

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;

topic Re: Counting wide variables and changing to long dataset in SAS Programming

Counting wide variables and changing to long dataset

Re: Counting wide variables and changing to long dataset

Re: Counting wide variables and changing to long dataset