SAS datasets support only fixed-width character or double-precision floating-point numeric data. The DATA step and most base SAS procedures can only process those two data types. Databases typically support various additional ANSI data types, including DECIMAL(NUMERIC), VARCHAR, INT, BIGINT, and more. In base SAS FedSQL and DS2 can process these extra data types, but PROC SQL cannot. It is important to remember that, even if you successfully process VARCHAR or DECIMAL data in SAS, you must write the results to a database table to preserve those data types. Saving the result as a SAS data set automatically converts all data types to fixed-width character or double-precision floating-point numeric data types. For this discussion, I'll use these two tables, one a SAS data set, and the other an Oracle table:
To resolve an SQL expression, all operands must be the same data type. Automatic data type conversion is not supported in SQL.
proc sql; select fractional+numtext as Total from sas.myTable; quit; proc fedsql; select fractional+numtext as Total from sas.myTable; quit proc sql; select fractional+numtext as Total from sas.myTable; ERROR: Expression using addition (+) requires numeric types. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. … proc fedsql; select fractional+numtext as Total from sas.myTable; ERROR: Operator does not exist: DOUBLE + CHAR ERROR: No operator matches the given name and argument type(s). You might need to add explicit typecasts. In FedSQL and native database SQL, you can explicitly convert data types using the CAST function. As noted by @Sajid01, PROC SQL does not support the CAST function but instead relies on the base SAS PUT and INPUT functions for data type conversion.
title "PROC SQL Results"; proc sql; select sum(fractional+input(numtext,32.)) as Total from db.myTable; quit; title "PROC FedSQL Results"; proc fedsql iptrace; select sum(fractional+cast(numtext as double)) as Total from db.myTable; quit;The advantage of using PROC FedSQL in this case is that the SQL processing can be pushed into the database, minimizing data movement. Because PROC SQL uses non-ANSI code to convert the data type, the processing must be done in SAS.
proc sql; select fractional+input(numtext,32.) as Total from db.myTable; SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error. ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing. ORACLE_24: Prepared: on connection 0 SELECT "FRACTIONAL", "NUMTEXT" FROM EDU.MYTABLE ORACLE_25: Executed: on connection 0 SELECT statement ORACLE_24 … proc fedsql iptrace; select fractional+cast(numtext as double) as Total from db.myTable; IPTRACE: FULL pushdown to ORACLE SUCCESS! IPTRACE: Retextualized child query:select SUM (("EDU"."MYTABLE"."FRACTIONAL"+ cast("EDU"."MYTABLE"."NUMTEXT" as DOUBLE PRECISION))) as "TOTAL" from "EDU"."MYTABLE"Note that PROC SQL had to bring the data to SAS to do the type conversion and summarization, but by using the CAST function, PROC FedSQL manage to get full push-down to the database.