CODEWITHSUNDEEP
sqloracle-databasesas
user516688
user516688

Reputation: 91

Why does this SQL query not run in Base SAS?

proc sql;

select sum(counts) 
from (
      select count(*) "counts" from table1
      union
      select count(*) "counts" from table2
    );
quit;

I keep receiving the below error. I even tried removing the alias.

ERROR: The SUM summary function requires a numeric argument.
ERROR: The following columns were not found in the contributing tables: counts.

Upvotes: 0

Views: 296

Answers (3)

Tom
Tom

Reputation: 51611

Your code is giving the inner COUNT(*) results a label, but it is not giving them a variable name.

SAS PROC SQL will let you write code where you don't give your variables names when just doing a SELECT or even when doing a SELECT *.

 select count(*) "Total number of rows" from sashelp.class ;
 select * from
   (select count(*) "Label 1" from sashelp.class 
    union all 
    select count(*) "Label 2" from sashelp.class
   )
 ;

You can even use them to make datasets, in which case SAS will generate a variable name. Try this:

 create table test1 as 
  (select count(*) "Total number of rows" from sashelp.class)
 ;
 describe table test1 ;

But if you want to use the variable in an expression like SUM() then you have to give the variable a name and not just a label. Otherwise you don't know what to put inside the aggregate function call. In general it is a good idea to give your derived variables names.

 select sum(counts) as total_count label="Total of count(*)" from
   (select count(*) as counts from sashelp.class 
    union all 
    select count(*) as counts from sashelp.class
   )
 ;

You could try add the DQUOTE=ANSI option to your PROC SQL statement. This will treat "name" as a variable name instead of a string. But you would still need to add the AS keyword before the name.

proc sql dquote=ansi;
select sum(counts)
 from (
  select count(*) as "counts" from sashelp.class
  union
  select count(*) as "counts" from sashelp.class
      )
;
quit;

Upvotes: 1

vasja
vasja

Reputation: 4792

In SAS, identifiers are typically used as not quoted. In case you want identifier containing space or other special character you can use "identifier"N to indicate the quoted string is the Name ("my counts"n below).

As for the query, I'd use union all rather then union to sum the counts correctly.

proc sql;
create table table1 (x num);
create table table2 (x num);
proc sql;
select sum(counts) as "my counts"n 
from (
      select count(*) as counts from table1
      union all
      select count(*) from table2
    );
quit;

For SAS tables, you might get better performance by using row numbers from SAS dictionary tables like this:

proc sql;
select sum(nobs - delobs) as "my counts"n 
from dictionary.tables 
where libname = 'WORK' and memname in ('TABLE1', 'TABLE2');
quit;

Upvotes: 3

Mihai
Mihai

Reputation: 26784

In oracle if you use identifiers, you always have to refer to the column(s) with those identifiers.Of course if counts is not a reserved word,you can remove them altogether.

select sum("counts") 
from (
      select count(*) "counts" from table1
      union
      select count(*) "counts" from table2
    );
quit;

Upvotes: 0

Related Questions