topic Re: Proc SQL in SAS Programming

Proc SQL

newbi — Fri, 18 Feb 2011 19:14:46 GMT

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

Re: Proc SQL

Vasile01 — Fri, 18 Feb 2011 19:31:21 GMT

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

Re: Proc SQL

newbi — Fri, 18 Feb 2011 20:44:56 GMT

Are you saying :

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

Re: Proc SQL

DBailey — Fri, 18 Feb 2011 22:11:19 GMT

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;

Re: Proc SQL

chang_y_chung_hotmail_com — Fri, 18 Feb 2011 22:38:32 GMT

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;

Re: Proc SQL

newbi — Fri, 18 Feb 2011 23:08:57 GMT

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;

Re: Proc SQL

newbi — Mon, 21 Feb 2011 20:27:00 GMT

Can i get the same result using Data Step ?

Thanks

Re: Proc SQL

Peter_C — Tue, 22 Feb 2011 09:01:08 GMT

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]

Re: Proc SQL

newbi — Tue, 22 Feb 2011 17:47:51 GMT

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.

Re: Proc SQL

Peter_C — Tue, 22 Feb 2011 23:15:31 GMT

have a look at the LAG() function