DATA Step, Macro, Functions and more

Proc SQL

Reply
SAS Employee
Posts: 73

Proc SQL

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
Occasional Contributor
Posts: 14

Re: Proc SQL

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
SAS Employee
Posts: 73

Re: Proc SQL

Are you saying :

Select Year, Qtr, Sales, (Select sum(Sales) From tbl1) as Total
From table1
Super Contributor
Posts: 578

Re: Proc SQL

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;
Regular Contributor
Posts: 241

Re: Proc SQL

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 (200911000)


      values (200921000)


      values (200931000)


      values (200941000)


      values (201011550)


      values (201021550)


      values (201031550)


      values (201041550);


 


      /* 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;

SAS Employee
Posts: 73

Re: Proc SQL

Posted in reply to chang_y_chung_hotmail_com
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;
SAS Employee
Posts: 73

Re: Proc SQL

Can i get the same result using Data Step ?

Thanks
Valued Guide
Posts: 2,177

Re: Proc SQL

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]
SAS Employee
Posts: 73

Re: Proc SQL

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.
Valued Guide
Posts: 2,177

Re: Proc SQL

have a look at the LAG() function
Ask a Question
Discussion stats
  • 9 replies
  • 396 views
  • 0 likes
  • 5 in conversation