Help using Base SAS procedures

SAS/ACCESS

Reply
Contributor DCL
Contributor
Posts: 40

SAS/ACCESS

Hi,

I have a requirement to copy some oracle data from one table to another using SAS.

I am using the following libname statement to retrieve the oracle tables present in the schema-uuu

Libname xxx ORACLE USER=yyy PASSWORD=vvv SCHEMA=uuu PATH=www;

One of my Oracle table has a field with data type as float. But when they are transformed to a SAS dataset, this field is transformed to a numeric variable with format best12. . As a result the value .00000081147986 in oracle table becomes 8.1147986E-6 in the sas dataset.

Is there a way by which I can specify the format of a numeric value in the libname statement to make it best22. similar to DBMAX_TEXT option.

Thanks in advance!
Respected Advisor
Posts: 4,173

Re: SAS/ACCESS

Hi

I think this is a case where you should use Proc SQL Passthrough facility. It doesn't make a lot of sense to me to first load everything from Oracle into SAS only to reload it directly into another Oracle table (without any transformation steps).

You could for example develop your code using SQLDeveloper (free tool, can be downloaded from Oracle) or TOAD. Looks like this would be a simple SQL INSERT or eventually a UPSERT.

Once you've got working Oracle SQL you just paste it into a PROC SQL passthrough block.

HTH
Patrick Message was edited by: Patrick
Ask a Question
Discussion stats
  • 1 reply
  • 136 views
  • 0 likes
  • 2 in conversation