BookmarkSubscribeRSS Feed
metallon
Pyrite | Level 9

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

3 REPLIES 3
SASKiwi
PROC Star

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
Super User

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
Tourmaline | Level 20
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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 2605 views
  • 0 likes
  • 4 in conversation