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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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