DATA Step, Macro, Functions and more

number formatting in proc sql passthrough or oracle

Reply
Frequent Contributor
Posts: 133

number formatting in proc sql passthrough or oracle

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

Super User
Posts: 3,254

Re: number formatting in proc sql passthrough or oracle

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? 

Super User
Posts: 11,343

Re: number formatting in proc sql passthrough or oracle

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.

Super User
Posts: 5,430

Re: number formatting in proc sql passthrough or oracle

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).
Data never sleeps
Ask a Question
Discussion stats
  • 3 replies
  • 221 views
  • 0 likes
  • 4 in conversation