topic proc sql sumation in SAS Programming

proc sql sumation

dsbihill — Fri, 13 Jun 2014 12:58:48 GMT

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 .

Re: proc sql sumation

CTorres — Fri, 13 Jun 2014 13:19:26 GMT

Complete the solution with Proc TRANSPOSE:

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

by part;

id year;

var quantity;

quit;

Regards,

Re: proc sql sumation

slchen — Fri, 13 Jun 2014 13:28:05 GMT

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;

Re: proc sql sumation

new4new — Fri, 13 Jun 2014 13:44:20 GMT

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;

Re: proc sql sumation

stat_sas — Fri, 13 Jun 2014 14:05:32 GMT

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;