DATA Step, Macro, Functions and more

Vertical summation

Reply
Contributor
Posts: 51

Vertical summation

I am attempting to do a column same sorted by two variables with proc sql but get the error message below:

 

proc sql; 

create table sales as

select model, year

sum(revenue) as sum_revenue,

from carsales

         _ 

         22

         76

Error 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &....

Error 76-322: Syntax error, statement will be ignored.

group by model, year;

run;

 

Have 

model year revenue

acura  2013  15000

acura 2014    30000

acura 2014    150000

hyundai 2012   300000

hyundai 2012 300000

bmw 2007 1000000

bmw 2007 1000000

bmw 2007 1000000

 

Want 

model year revenue sum_revenue

acura  2013  15000 15000

acura 2014    30000 180000

acura 2014    150000 180000

hyundai 2012   300000 600000

hyundai 2012 300000 600000

bmw 2007 1000000 3000000

bmw 2007 1000000 3000000

bmw 2007 1000000 3000000

 

Help?

 

Super User
Posts: 23,247

Re: Vertical summation

[ Edited ]
Posted in reply to Wafflecakes

You’re missing a comma after YEAR. And no commas before FROM. 

Frequent Contributor
Posts: 115

Re: Vertical summation

Posted in reply to Wafflecakes

You are missing a comma after year.  You don't need the comma after sum_revenue.

 

proc sql ;
	create table want as 
	select model, 
	       year, 
	       revenue, 
	       sum(revenue) as sum_revenue 
	  from carsales 
	    group by model, year;
quit;
Super User
Super User
Posts: 7,932

Re: Vertical summation

Posted in reply to Wafflecakes

If find that you will lose track of where your commas are much less if you place them at the beginning of your continuation lines instead of at the end. It is much easier to scan the beginning of the lines of a block of code than the end of the lines.

proc sql; 
create table sales as
  select model
       , year
       , sum(revenue) as sum_revenue
  from carsales
  group by model, year
;
quit;
Ask a Question
Discussion stats
  • 3 replies
  • 203 views
  • 0 likes
  • 4 in conversation