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.
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.