topic Re: Sum numbered range lists in pro sql in SAS Programming

Sum numbered range lists in pro sql

Macro — Tue, 19 Mar 2013 16:19:08 GMT

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?

Re: Sum numbered range lists in pro sql

Reeza — Tue, 19 Mar 2013 16:35:49 GMT

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.

Re: Sum numbered range lists in pro sql

Macro — Tue, 19 Mar 2013 19:33:31 GMT

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?

Re: Sum numbered range lists in pro sql

PGStats — Tue, 19 Mar 2013 20:10:48 GMT

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;

Re: Sum numbered range lists in pro sql

DBailey — Tue, 19 Mar 2013 20:15:46 GMT

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;

Re: Sum numbered range lists in pro sql

PGStats — Tue, 19 Mar 2013 21:12:08 GMT

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

Re: Sum numbered range lists in pro sql

DBailey — Tue, 19 Mar 2013 21:21:00 GMT

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;

Re: Sum numbered range lists in pro sql

Tom — Tue, 19 Mar 2013 21:30:08 GMT

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

sum(0,&names) as count

Re: Sum numbered range lists in pro sql

Reeza — Tue, 19 Mar 2013 21:31:37 GMT

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;

Re: Sum numbered range lists in pro sql

DBailey — Thu, 21 Mar 2013 11:30:09 GMT

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

Re: Sum numbered range lists in pro sql

Macro — Thu, 21 Mar 2013 17:33:22 GMT

Thanks everyone for so many responses. Very good discussion.

Re: Sum numbered range lists in pro sql

Macro — Thu, 21 Mar 2013 18:49:57 GMT

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.

Re: Sum numbered range lists in pro sql

PGStats — Thu, 21 Mar 2013 20:09:24 GMT

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;

Re: Sum numbered range lists in pro sql

Macro — Fri, 22 Mar 2013 14:31:44 GMT

Thanks PG. Good work.