CODEWITHSUNDEEP
sas
Anonamous
Anonamous

Reputation: 303

Single and Double Hash in Proc sql

I have 12 columns and I want to add them through sql. I have tried:

proc sql;
select*,sum(a1-a12) as total
from tablename;
quit;

However this isn't working. Is there an alternative or can we use single and double hash only in Data steps.

Upvotes: 1

Views: 186

Answers (3)

pinegulf
pinegulf

Reputation: 1396

In some cases you do not know how many variables there are or you don't want to hard code it. The syntax is in this case: sum(of < variable>:);

data test;
    a1=1; 
    a2=2; 
    /*number 3 is missing*/
    a4=4; 
    a5=5; 
run;

data test2;
    set test;
    sum_of_all_As= sum(of a:);
run;

For more tips and tricks see: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245953.htm

Upvotes: 0

Joe
Joe

Reputation: 63424

If you want this in SQL because you're making use of other SQL functionality in addition to this, make a view.

data have_v/view=have_v;
  set have;
  total = sum(of a1-a12);
run;

proc sql;
  select * from have_v;   *presumably you do other things here;
quit;

Upvotes: 0

Tom
Tom

Reputation: 51566

If you want to add values in the same observation then you need to use SAS function sum(,...) and not the SQL aggregate function sum(). You current code looks like the later since it only has one value listed, the difference between variables A1 and A12. This is because PROC SQL does not recognize variable lists. You will need to list all of your variables.

select *,sum(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12) as total
from have
;

Upvotes: 2

Related Questions