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

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

2 REPLIES 2
art297
Opal | Level 21

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
Calcite | Level 5 SKK
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 779 views
  • 3 likes
  • 3 in conversation