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;