Help using Base SAS procedures

sum function for charecter data type

Reply
N/A
Posts: 0

sum function for charecter data type

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.
Super Contributor
Super Contributor
Posts: 3,174

Re: sum function for charecter data type

Posted in reply to deleted_user
Look at using the INPUT function when selecting the variable yy_AMT inside the SUM( ) function specification.

Scott Barry
SBBWorks, Inc.
PROC Star
Posts: 1,760

Re: sum function for charecter data type

Posted in reply to deleted_user
You connect to oracle, but don't use the connection?
In other words, is the SQL following oracle's syntax or sas'?
N/A
Posts: 0

Re: sum function for charecter data type

Posted in reply to deleted_user
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.
PROC Star
Posts: 1,760

Re: sum function for charecter data type

Posted in reply to deleted_user
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;
Ask a Question
Discussion stats
  • 4 replies
  • 881 views
  • 0 likes
  • 3 in conversation