Sum numbered range lists in pro sql

[Macro]

Hi,

I have a numbered range list of varibles: count1 to count20 in my infile data file.  I would like to sum these varibles as count in proc sql

proc sql;

  create table outfile1 as

  select

    ID,

    sum(of Count1 - Count20) as count

  from infile;

but sas does not like this sum(of). So what is wrong with is code? Anyway to do this in sql?

[Reeza]

You can't use variable shortcuts in proc SQL. That will work fine in a data step, so either switch to a data step or list the variables in proc sql.

Also, in SQL and a data step it is a row operation, not column operation.

[Macro]

Thanks Reeza. If I really want to do this in sql, then listing the variable is impractical. How to do this in sql by macro?

[PGStats]

For a purely SAS/SQL solution, try :

proc sql noprint;

select name into:names separated by "," from dictionary.columns

where libname="WORK" and upcase(memname)="INFILE";

create table outfile1 as

select id, sum(&names) as count

from infile;

reset print;

select * from outfile1;

quit;

PG

[DBailey]

I think one change might be in order (separate by "+" instead of ",")?

proc sql noprint;

select name into:names separated by "+" from dictionary.columns

where libname="WORK" and upcase(memname)="INFILE";

create table outfile1 as

select id, sum(&names) as count

from infile

group by id;

reset print;

select * from outfile1;

quit;

[PGStats]

Thanks DBailey for noting the problem. It's the GROUP BY clause that was superfluous. SUM here is not a summary function. I edited my message to reflect this. - PG

[DBailey]

Actually, I think SUM in proc sql is an aggregation function.  If you just want to add the columns then how about

proc sql;

create table outfile1 as select id, &names as ColumnSum from infile;

quit;

[Tom]

There is an aggregate SUM() function in ANSI SQL that SAS supports, but PROC SQL also supports SAS functions, including the SUM(,) function. The compiler will treat all references to SUM() that only include one parameter as the aggregate function and any that include two more parameters as the SAS function.  Note that the SUM(,) function will get a different result from just using the addition operator when there are missing values.

To prevent the compiler from being confused when there is only one variable specified add an extra argument.

sum(.,&names) as count

or

sum(0,&names) as count

[DBailey]

You can tell those of us who grew up in a sql world...and then started using sas.

[Macro]

Thanks everyone for so many responses. Very good discussion.

[Reeza]

If you have more than one parameter to the sum function it operates across the row, If you have a single parameter then its an aggregate function. Same for average, min, max in SQL and probably some others.

proc sql;

    select *, sum(age, height, weight) as row_sum from sashelp.class;

    select *, sum(age) as sum_age, sum(height) as sum_height from sashelp.class;

quit;

[Macro]

Why separate by "+" not ","? Will &names contain ID variable or not? What if not only I have count1 to count20, I also have amount1 to amount20, and I want to sum all counti as count, and sum all amounti as amount? Will this selcet name statement still work or not? Thanks.

[PGStats · Accepted solution]

HI, I should have been more careful . ID didn't belong in the &names list. This is how it should be for summing counts and amounts separately :

proc sql noprint;

select name into:counts separated by "," from dictionary.columns

     where libname="WORK" and upcase(memname)="INFILE" and upcase(name) like "COUNT%";

select name into:amounts separated by "," from dictionary.columns

     where libname="WORK" and upcase(memname)="INFILE" and upcase(name) like "AMOUNT%";

create table outfile1 as

select id, sum(&counts) as count, sum(&amounts) as amount

from infile;

reset print;

select * from outfile1;

quit;

Note that the equivalent SAS datastep, using variable lists, is much simpler :

data outfile1(keep=id count amount);
set infile;
count = sum(of count:);
amount = sum(of amount:);
run;

proc print; run;

PG

[Macro]

Thanks PG. Good work.