Solved
Contributor
Posts: 53

# proc sql sumation

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          .

Accepted Solutions
Solution
‎06-13-2014 09:19 AM
Regular Contributor
Posts: 180

## Re: proc sql sumation

Complete the solution with Proc TRANSPOSE:

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

by part;

id year;

var quantity;

quit;

Regards,

All Replies
Solution
‎06-13-2014 09:19 AM
Regular Contributor
Posts: 180

## Re: proc sql sumation

Complete the solution with Proc TRANSPOSE:

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

by part;

id year;

var quantity;

quit;

Regards,

Super Contributor
Posts: 275

## Re: proc sql sumation

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;

Occasional Contributor
Posts: 6

## Re: proc sql sumation

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;

Posts: 1,270

## Re: proc sql sumation

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;

🔒 This topic is solved and locked.