proc sql sumation

[dsbihill]

I have the following data and query

data have;
   informat date date9. quantity 2.;
   input part $ date quantity;
   format date date9. quantity 2.;
   datalines;

A 01jan2013 10
A 01JUN2013 5
A 12DEC2013 4
A 01FEB2014 6
A 15JUN2014 20
B 20FEB2013 5
B 15JAN2013 6
;

proc sql;
    create table almostwant as
    select part, year(date) as year, sum(quantity) as quantity
        from have
    group by part, year
    ;

quit;

I would like to output this data so that each year is created as a new variable rather then in a list format.  So i would like to see the data look like below but i cant seem to get it to work.  Any suggestions would be greatly appreciated.

Want:

Part     2013qty      2014qty

A               19          26

B               11          .

[CTorres · Accepted solution]

Complete the solution with Proc TRANSPOSE:

proc transpose data=almostwant out=want(drop=_name_) prefix=QTY;

  by part;

  id year;

  var quantity;

quit;

Regards,

[slchen]

proc sql;
select part, max(_2013qty) as _2013qty,max(_2014qty) as _2014qty

from (

         select part, ifn(year(date)=2013,sum(quantity),0) as _2013qty,
               ifn(year(date)=2014, sum(quantity),0) as _2014qty
          from have group by part,year(date)

        ) group by part;
quit;

[new4new]

Hi dsbihill,

First of all you cannot have SAS variable names starting with numbers. So your ‘Want:’ example shows labels and not variable names.

It is a standard proc transpose task:

data have;

   informat date date9. quantity 2.;

   input part $ date quantity;

   format date date9. quantity 2.;

   datalines;

A 01jan2013 10

A 01JUN2013 5

A 12DEC2013 4

A 01FEB2014 6

A 15JUN2014 20

B 20FEB2013 5

B 15JAN2013 6

;

proc sql;

    create table almostwant as

    select part, cats(put(year(date), best4.), 'qty') as year lenght=8, sum(quantity) as quantity

        from have

    group by part, year

   order by 1, 2 ;

quit;

proc transpose data=almostwant

out=want(drop=quantity)

name=quantity;

by part;

id year;

idlabel year;

run;

[stat_sas]

data want;
set have;
year=year(date);
run;

proc tabulate data=want;
class part year;
var quantity;
table part,year=' '*quantity*sum=' '*f=8.0;
run;