BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Look at using the INPUT function when selecting the variable yy_AMT inside the SUM( ) function specification.

Scott Barry
SBBWorks, Inc.
ChrisNZ
Tourmaline | Level 20
You connect to oracle, but don't use the connection?
In other words, is the SQL following oracle's syntax or sas'?
deleted_user
Not applicable
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.
ChrisNZ
Tourmaline | Level 20
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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 2374 views
  • 0 likes
  • 3 in conversation