Convert oracle variable into number

[ciro]

Hi, 

I have large data stored in Oracle where multiple variables are set up as varchar2(4000 byte).

Some of them are in fact numbers but are stored with a +, and a bunch of leading zeros.  In the example (in the table attached there is an example of what I have) the number start with the first non zero value and it is to be divided by 100000.

I need only a relatively small subset of data in sas, obtained through joining another oracle table.

how do I get the data in SAS already converted into number so that they do not occupy su much space?

thank you very much in advance

[Kurt_Bremser · Accepted solution]

Posting datasets in data step code makes helping yopu much easier. This is the code that was created by the macro mentioned in my footnotes:

data HAVE;
  infile datalines dsd truncover;
  input a:$4000.;
datalines4;
+00000005609333000
+00000002399300000

+00000002477900000
+00000000026800000

+00000000770400000
+00000002178300000
+00000001558100000
+00000001694400000
;;;;
run;

Run this code against this dataset:

data want;
set have;
b = input(a,18.5);
format b 18.5;
keep b;
run;

proc print data=want noobs;
run;

Result:

          b

56093.33000
23993.00000
     .     
24779.00000
  268.00000
     .     
 7704.00000
21783.00000
15581.00000
16944.00000