BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
madamimadam
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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.

View solution in original post

5 REPLIES 5
Steelers_In_DC
Barite | Level 11

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
PROC Star

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
Calcite | Level 5

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 27265 views
  • 0 likes
  • 6 in conversation