How to use cast function in SAS

[dewanganrahul]

I have SQL Server queries which needs to be convert into SAS code. So, I am writing the queries under PROC SQL in SAS and using the cast function in SELECT CLAUSE, but I am getting error at place 'as'. PFB, the example:-

proc sql;

 select cast(variable_name as nvarchar) as Variable1 from table1;

quit;

I am getting the error at first 'as' place and showing me the syntax error.

[Sajid01]

SAS Proc SQL does not support CAST function. You have to use put function to convert a numeric value to a character AND input function to convert a character value to a numeric.
CAST  is however  available  in Federated SQL in SAS.

[Adriaan_Gouws]

Good day

An alternative approach to your query can be written as follow. I've also included conversion from character to numeric, and numeric to character.

/*Dummy data*/
data have;
	infile datalines;
	input _char $ _num;
	datalines;
A 504505
B 454505
C 554505
D 604505
E 404505
F 434505
;
run;

/*Convert character to numeric, or convert numeric to character*/
proc sql;
	create table want as
		select 
			_char format=$10. as Variable_CHAR
			,_num format=10. as Variable_NUM
			,input(_char,10.) format=10. as Variable_CHAR2NUM
			,put(_num,10.) format=$10. as Variable_NUM2CHAR
		from have;
quit;

[Tom]

Why attach formats to the variables?

The only places you might need to do something is when you need VARIABLE_CHAR to be a different LENGTH than _CHAR.  Attaching a format that only displays the first 10 bytes is different than telling SAS to only store 10 bytes.

There could be value in attaching the 10. format specification to VARIABLE_NUM or VARIABLE_CHAR2NUM, but in most situations SAS will use BEST12. format to display numbers, so that should work fine for 10 digit integers.

_char length=10 as Variable_CHAR
,_num as Variable_NUM
,input(_char,10.) as Variable_CHAR2NUM
,put(_num,10.) as Variable_NUM2CHAR

[SASKiwi]

Why do you need to convert your SQL Server SQL queries to SAS SQL? If you still want to keep running your queries on SQL Server from SAS, just use SQL Passthru and don't change your SQL at all (CAST etc will work fine from SAS). If you have a lot of code to convert this will save a lot of effort.

[SASJedi · Accepted solution]

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

Log:

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;

Results:

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.