Question with Aggregating column data

Reply
Frequent Contributor
Posts: 101

Question with Aggregating column data

Hello,

I have sasdata that takes the following general format:

ID                           DATE                            SALES

001                         20010131                         100

001                         20010228                         50

001                         20010331                         150

...                              .....                                ....

001                         20020131                          75

001                         20020228                          15

....                              .....                              .....

002                         20010131                         50

002                         20010228                         54

.....                              .....                              .....

So essentially, I have  bunch of monthly data for a bunch of different companies (IDs).   What I want to do is aggregate YEARLY totals for each company, so that I get a table that returns three columns: ID, YEAR, ANNUAL SALES.

There are 2 caveats:

-I would like to do this in proc sql, since that is what I am doing for the rest of my code and what I am comfortable with

-The date range for the companies (IDs) is DIFFERENT FOR EACH COMPANY.  Meaning that the start date and end date of when I have sales data is never the same

Any help would be great appreciated!!

Super User
Posts: 19,772

Re: Question with Aggregating column data

Posted in reply to mahler_ji

If you don't have partial years something like the  following will work:

proc sql;

     create table want as

select id, substr(date, 1, 4) as year, sum(sales) as annual_sales

from have

group by id, calculated year

order by id, calculated year;

quit;

Frequent Contributor
Posts: 101

Re: Question with Aggregating column data

Reeza,

Thank you very much for your reply!  Will this sum only the sales from the year in question? Meaning, will this not just sum up all of the sales for ALL years?

I am going to give it a try and see how it turns out!

Thanks,

John

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