topic Re: number formatting in proc sql passthrough or oracle in SAS Programming

number formatting in proc sql passthrough or oracle

metallon — Fri, 05 Feb 2016 07:32:39 GMT

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

Re: number formatting in proc sql passthrough or oracle

SASKiwi — Fri, 05 Feb 2016 08:25:46 GMT

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?

Re: number formatting in proc sql passthrough or oracle

ballardw — Fri, 05 Feb 2016 16:21:30 GMT

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.

Re: number formatting in proc sql passthrough or oracle

LinusH — Fri, 05 Feb 2016 16:52:50 GMT

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