Proc SQL

[newbi]

I have table with four columns as: Year, Qtr and Sales. I would like to create query that would give me yearly total with qtr in query.

Year Qtr Sales
2009 1 $1000
2009 2 $1000
2009 3 $1000
2009 4 $1000

Result:

Year Qtr Sales YrTotal
2009 1 $1000 $4000
2009 2 $1000 $4000
2009 3 $1000 $4000
2009 4 $1000 $4000


Thanks

[Vasile01]

Hi,

Start by summarizing the variable of interest, like:

proc sql;
select sum(var_data) as year_data from many_data;
quit;

Warm regards,
Vasile

[newbi]

Are you saying :

Select Year, Qtr, Sales, (Select sum(Sales) From tbl1) as Total
From table1

[DBailey]

proc sql;
select
t1.year,
t1.qtr,
t1.sales,
t2.ytd_sales
from tablea as t1 inner join (select year, sum(sales) as ytd_sales from tablea group by year) as t2
on t1.year=t2.year;
quit;

[chang_y_chung_hotmail_com]

In proc sql, the summary stats are automatically (re-)merged to the original data, unless you disable this with noremerge option to the proc sql statement, or nosqlremerge system option.

   proc sql;

      /* test data */

      create table one (year num, qtr num, sales num);

      insert into one

      values (2009, 1, 1000)

      values (2009, 2, 1000)

      values (2009, 3, 1000)

      values (2009, 4, 1000)

      values (2010, 1, 1550)

      values (2010, 2, 1550)

      values (2010, 3, 1550)

      values (2010, 4, 1550);

      /* attach yearly sum */

      create table two as

      select year, qtr, sales format=dollar8.

           , sum(sales) as total format=dollar8.

      from   one

      group by year

      order by year, qtr;

      /* check */

      select * from two;

      /* on lst

      year       qtr     sales     total

      --------------------------------------

       2009         1    $1,000    $4,000

       2009         2    $1,000    $4,000

       2009         3    $1,000    $4,000

       2009         4    $1,000    $4,000

       2010         1    $1,550    $6,200

       2010         2    $1,550    $6,200

       2010         3    $1,550    $6,200

       2010         4    $1,550    $6,200

      */

   quit;

[newbi]

Thanks for the quick reply everyone.

I used the following sql

select year, qtr, sales format=dollar8.

, sum(sales) as total format=dollar8.

from one

group by year

order by year, qtr;

[newbi]

Can i get the same result using Data Step ?

Thanks

[Peter_C]

A data step solution going from
inputDS
Year Qtr Sales
2009 1 $1000
2009 2 $1000
2009 3 $1000
2009 4 $1000

Result:
Year Qtr Sales YrTotal
2009 1 $1000 $4000
2009 2 $1000 $4000
2009 3 $1000 $4000
2009 4 $1000 $4000

[pre]data result ;
set inputDS(in=a) inputDS ; * passing through data twice;
by year ; * in YEAR groups ;
if first.year then yrTotal = . ; * clearing first pass accumulator;
if a then yrTotal + Sales ; * accumulating in first pass ;
if not a ; * outputing no data from first pass ( but accumulator is RETAINed);
run ;[/pre]

[newbi]

Thanks for the reply.

I have one more question. I have query that outputs data as:

Yr Sales
2008 $40000
2009 $10000
2010 $50000

I would like to add another column for Previous Yr Sales

Yr Sales PreYrSales
2008 $40000 $0
2009 $10000 $40000
2010 $50000 $10000

Thanks in advance.

[Peter_C]

have a look at the LAG() function