DATA Step, Macro, Functions and more

proc sql sumation

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

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,

View solution in original post


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;

Trusted Advisor
Posts: 1,228

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 231 views
  • 3 likes
  • 5 in conversation