BookmarkSubscribeRSS Feed
newbi
SAS Employee
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
9 REPLIES 9
Vasile01
Fluorite | Level 6
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
SAS Employee
Are you saying :

Select Year, Qtr, Sales, (Select sum(Sales) From tbl1) as Total
From table1
DBailey
Lapis Lazuli | Level 10
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
Obsidian | Level 7
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;

newbi
SAS Employee
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
SAS Employee
Can i get the same result using Data Step ?

Thanks
Peter_C
Rhodochrosite | Level 12
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
SAS Employee
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
Rhodochrosite | Level 12
have a look at the LAG() function

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1773 views
  • 0 likes
  • 5 in conversation