DATA Step, Macro, Functions and more

Sum numbered range lists in pro sql

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Sum numbered range lists in pro sql

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?


Accepted Solutions
Solution
‎03-21-2013 04:09 PM
Respected Advisor
Posts: 4,649

Re: Sum numbered range lists in pro sql

HI, I should have been more careful Smiley Sad. 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 countSmiley Happy;
amount = sum(of amountSmiley Happy;
run;

proc print; run;

PG

PG

View solution in original post


All Replies
Super User
Posts: 17,837

Re: Sum numbered range lists in pro sql

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.

Contributor
Posts: 50

Re: Sum numbered range lists in pro sql

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?

Respected Advisor
Posts: 4,649

Re: Sum numbered range lists in pro sql

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

PG
Super Contributor
Posts: 578

Re: Sum numbered range lists in pro sql

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;

Respected Advisor
Posts: 4,649

Re: Sum numbered range lists in pro sql

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

PG
Super Contributor
Posts: 578

Re: Sum numbered range lists in pro sql

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;

Super User
Super User
Posts: 6,500

Re: Sum numbered range lists in pro sql

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

Super Contributor
Posts: 578

Re: Sum numbered range lists in pro sql

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

Contributor
Posts: 50

Re: Sum numbered range lists in pro sql

Thanks everyone for so many responses. Very good discussion.

Super User
Posts: 17,837

Re: Sum numbered range lists in pro sql

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;

Contributor
Posts: 50

Re: Sum numbered range lists in pro sql

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.

Solution
‎03-21-2013 04:09 PM
Respected Advisor
Posts: 4,649

Re: Sum numbered range lists in pro sql

HI, I should have been more careful Smiley Sad. 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 countSmiley Happy;
amount = sum(of amountSmiley Happy;
run;

proc print; run;

PG

PG
Contributor
Posts: 50

Re: Sum numbered range lists in pro sql

Thanks PG. Good work.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1646 views
  • 8 likes
  • 5 in conversation