CrossTab Aggregation Frustration

Reply
Occasional Contributor
Posts: 10

CrossTab Aggregation Frustration

Hi All,

I have a table which looks like:

Make1 Year Old2 Year Old3 year old
Ford99%
Mitsubishi43%
Fiat23%
Ford66%
Mitsubishi55%
Fiat87%
Ford24%
Mitsubishi25%
Fiat66%

Is there such a thing in SAS that will aggreagte these together so that all the numbers are in the relevant columns but grouped by the first 'make' column.

It seems such a simple thing but i just cannot make it work.

Super Contributor
Posts: 578

Re: CrossTab Aggregation Frustration

possibly...depending on the values of your data:

proc sql;

create table want as

select

     make,

     max(year_1) as year_1,  

     max(year_2) as year_2,

    max(year_3) as year_3

from work.have

group by make;

quit;,

Super Contributor
Posts: 644

Re: CrossTab Aggregation Frustration

You could use a datastep with a merge, or Proc SQL.  The key is to treat the source data as 3 separate tables

proc sql;

     create table want as

          select a.make

               ,     a.yr_1

               ,     b.yr_2

               ,     c.yr_3

          from     have a

               ,     have b

               ,     have c

          where     a.make = b.make

               and   a.make = c.make

               and not (a.yr_1 is null)

               and not (b.yr_2 is null)

               and not (c.yr_3 is null)

                    ;

quit;

Ask a Question
Discussion stats
  • 2 replies
  • 192 views
  • 0 likes
  • 3 in conversation