number formatting in proc sql passthrough or oracle

[metallon]

Hi,

I wrote some proc sql to retrieve data from our Oracle DB.

Unfortunately the number formatting is a bit unfortunate on the Oracle side,

some Excel ETL process did not fix it now I have to do it on the reporting side.

proc sql;

   connect to oracle as xxx (user=xxx password=xxxx' path=DATABASE);

     create table mytab as

     (

     select * from connection to xxx

     (  

SELECT column from table

   );

   disconnect from xxx;

quit;

The column shows:

1000,000

0,010

1,000

-

null

>SGF

So its not all numbers really but if its a number I need to format it correctly i.e.

without decimal places if they are just trailing 0

[SASKiwi]

In the SAS dataset/table MYTAB, what is the type of the variable COLUMN - character or numeric? Also what is the SAS format for the variable if any? 

[ballardw]

Your example data looks like it may have a fixed decimal format with a forced 3 decimal values, possibly something like 10.3 . The values are no different  but if you do not want trailing zeroes shown then assign a BEST format like BEST10.

[LinusH]

Why are you using explicit pass thru?
Nothing in your query indicates that you'll benefit from that.

As you say, it seems to be a char column. If you issue a libname to that schema you could use proc contents to see how SAS maps this column (by default).