BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Macro
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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 count:);
amount = sum(of amount:);
run;

proc print; run;

PG

PG

View solution in original post

13 REPLIES 13
Reeza
Super User

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
Obsidian | Level 7

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
Opal | Level 21

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
DBailey
Lapis Lazuli | Level 10

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
Opal | Level 21

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
DBailey
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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
Lapis Lazuli | Level 10

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

Macro
Obsidian | Level 7

Thanks everyone for so many responses. Very good discussion.

Reeza
Super User

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
Obsidian | Level 7

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
Opal | Level 21

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 count:);
amount = sum(of amount:);
run;

proc print; run;

PG

PG
Macro
Obsidian | Level 7

Thanks PG. Good work.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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