topic Re: sum function for charecter data type in SAS Procedures

sum function for charecter data type

deleted_user — Tue, 15 Sep 2009 18:45:41 GMT

I need to apply sum function to a character data type from a sas data set using PROC SQL. I am trying to use cast and to_number but gives me a error message "needs a numeric argument for sum function
the code is something like this.
PROC SQL;
Connect to oracle.....
create table WORK.xx as
( SELECT "xyz" as File_name,
Count(*) AS members,
sum(yy_AMT) as yy_AMT
FROM WORK.zzz
);
quit;
where yy_amt was defined as charecter data type in work.zzz dataset.

Re: sum function for charecter data type

sbb — Tue, 15 Sep 2009 18:52:33 GMT

Look at using the INPUT function when selecting the variable yy_AMT inside the SUM( ) function specification.

Scott Barry
SBBWorks, Inc.

Re: sum function for charecter data type

ChrisNZ — Wed, 16 Sep 2009 01:42:47 GMT

You connect to oracle, but don't use the connection?
In other words, is the SQL following oracle's syntax or sas'?

Re: sum function for charecter data type

deleted_user — Wed, 16 Sep 2009 14:52:19 GMT

Chris,
I did not understand your question. But from what I understood. Yes I am using PROC SQL and connecting to oracle and the select statement is in the SQL syntax.What did you mean by your question? What are you trying to hint?Let me know. So, that I can think on the correct lines.

Re: sum function for charecter data type

ChrisNZ — Thu, 17 Sep 2009 00:35:38 GMT

Your code should either be

PROC SQL;
Connect to oracle.....
create table WORK.xx as select * from connection to oracle (
SELECT "xyz" as File_name,
Count(*) AS members,
sum(yy_AMT) as yy_AMT
FROM WORK.zzz
);
quit;

or

PROC SQL;
create table WORK.xx as
SELECT "xyz" as File_name,
Count(*) AS members,
sum(yy_AMT) as yy_AMT
FROM WORK.zzz
;
quit;

The ANSI part of the syntax is the same, but there can be differences on "extensions" like sas dataset options or oracle partitions.

In the 1st case you must use oracle syntax (and functions like to_number()) inside the connection brackets. You must use sas syntax outside (where the select * is).

In the 2nd case you must use sas syntax (and functions like input() that Scott suggested). This works:

data T;
X='1';output;
X='2';output;
proc sql;
select sum(input(X,1.)) from T;
quit;

This works too:

proc sql;
connect to ORACLE ...
select * from connection to oracle (
select sum(to_number(APPLNT_LOCL_ID)) from ... where ROWNUM ≤ 10);
disconnect from oracle;
quit;