Change Field Data Type from Numeric to Character in PROC SQL

[madamimadam]

I need to change a data type for a field (field1) that I am querying from numeric to character. Here is the code that runs and provides the field back as a numeric data type using PROC SQL. I have been able to convert the number to a character using the Data statement along with the put function but can thus be accomplished in the PROC SQL statement?

proc sql;

connect to oracle as example(user=***** pw=***** path=abcd.xxxxx.com);

create table work.data as

select *

from connection to example

(

select field1, field2, from example.Address

where Address_Type = 'PRIMARY'

);

quit;

Here is what I have tried but get the following error message

proc sql;

connect to oracle as example (user=xxxxx pw=xxxx path=abcd.xxxxx.com.com);

create table work.data as

select *

from connection to example

(

select field1 as put(field1, $15.), field2 from example.Agency_Address

where Address_Type = 'PRIMARY'

);

quit;

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: select field1 as

       left(put(field1, $15.)), field2 from Example.Agency_Address where Address_Type = 'PRIMARY'.

Thanks in advance for your time.

[Steelers_In_DC · Accepted solution]

The put function you are using will work the same way if you use proc sql;, what you are showing here is passing through to oracle, you will need to use a function that works with the database you are connecting too, not something that will work within SAS.

[Astounding]

The PUT function always returns a character string.

The second parameter (in this case $15.) must use a dollar sign when the first parameter is character, and must omit the dollar sign when the first parameter is numeric.

Since you're making a numeric to character conversion, try removing the dollar sign:

put(field1, 15.)

[Keith0001]

Since you are using pass through functionality you can put the transformation into either the SAS part of the statement or the Oracle part of the statement.

If you're going to place the transformation into the oracle statement, use oracle language, for instance TO_CHAR .

[Kurt_Bremser]

Do the conversion in SAS:

proc sql;

connect to oracle as example (user=xxxxx pw=xxxx path=abcd.xxxxx.com.com);

create table work.data as

select put(field1,15.) as field1, field2

from connection to example

(

select field1, field2 from example.Agency_Address

where Address_Type = 'PRIMARY'

);

quit;

Also note the reverse ordering of the select ... as ... (function first, name second)

[RW9]

The above posters have given you the answer here, in pass-through code the syntax must match the Database syntax requirements.  In SAS SQL you can use SAS syntax rules.  Maybe if you want to combine the two (and of course, avoiding the * syntax totally, because that would just be guessing!):

proc sql;

     connect to oracle as example (user=xxxxx pw=xxxx path=abcd.xxxxx.com.com);

     create table work.data as

/* This part is processed by SAS and must conform to SAS syntax */

     select     put(FIELD1,best.) as FIELD1,

                     FIELD2 as FIELD2

     from connection to example

/* This part is passed to the database and must conform to the database syntax */

          (

               select field1, field2 from example.Agency_Address

               where Address_Type = 'PRIMARY'

          );

quit;