BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mr_Breeze
Calcite | Level 5

I have a variable that I am importing from a flat file. It contains spaces, alpha characters and numbers. I am importing it using input with a length of 32 ($32).

My problem is that some of the numbers are showing up in exponential format (see examples below). Anyone have a suggestion on how to correct this, so that all numerics show the full number?

Acct_Nbr

1.00009E+12

7200025802

1.01018E+12

6581202097

6.06005E+13

7441258501

5.20004E+11

900-161-6121

323553729

2.00004E+12

4.07028E+19

662631175

1.53495E+11

662631175

4127400093

ABC 123324

Thanks in advance for the assistance!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I would think that you are trying to accomplish something like the following:

data TC1;

  infile "path\today.csv"

  delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;

  informat Beneficiary_Account $32. ;

  format Beneficiary_Account $32. ;

  input  Beneficiary_Account;

  if not missing(input(Beneficiary_Account, ?? best32.))

   then Beneficiary_Account=strip(put(input(

     Beneficiary_Account,best32.),best32.));

run;

View solution in original post

14 REPLIES 14
ChendhilKumar
Calcite | Level 5

Mr Breeze,

Could you post some input data and the sas code you are using..?

Regards,

Chendhil

art297
Opal | Level 21

It would help if you provide some example incoming data and the code you are using to import the file.  You may only have to apply a numeric format (like best32.).

Mr_Breeze
Calcite | Level 5

  Here's part of my code:

data TC1;

  infile "path\today.csv"

  delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;

  informat Beneficiary_Account $32. ;

  format Beneficiary_Account $32. ;

input  Beneficiary_Account $;

run;

Here's an example of the incoming data (csv file):

27569276

27569276

5.102E+11

LU520023190087756900

8377715

11937232

1.0023E+11

501-408899-3

8.01411E+13

2323339

5103366633

1.6023E+11

1.00021E+13

820930

1700008145

2.72304E+13

7881283120

8.98002E+11

5221725656

CM128804

Tom
Super User Tom
Super User

Character strings don't know or care if the string looks like a number in scientific notation.

So what is it that you want to change? 

Do you want to convert the scientific notation character strings in the input CSV file into some other format?  What format do you want. 

Sounds like the real problem might be in whatever process generated the CSV file.  Do you have control over that process?

art297
Opal | Level 21

I would think that you are trying to accomplish something like the following:

data TC1;

  infile "path\today.csv"

  delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;

  informat Beneficiary_Account $32. ;

  format Beneficiary_Account $32. ;

  input  Beneficiary_Account;

  if not missing(input(Beneficiary_Account, ?? best32.))

   then Beneficiary_Account=strip(put(input(

     Beneficiary_Account,best32.),best32.));

run;

MikeZdeb
Rhodochrosite | Level 12

Hi ... just a suggestion about Art's code, this

Beneficiary_Account=strip(put(input(Beneficiary_Account,best32.),best32.));


can be replaced by

Beneficiary_Account=cats(input(Beneficiary_Account,best32.));

Howles
Quartz | Level 8

Also get rid of the "best" in the informat. It's just an alias, one which reinforces the misconception that something is being optimized.

Beneficiary_Account=cats(input(Beneficiary_Account,32.));


MikeZdeb wrote:

Hi ... just a suggestion about Art's code, this

Beneficiary_Account=strip(put(input(Beneficiary_Account,best32.),best32.));


can be replaced by

Beneficiary_Account=cats(input(Beneficiary_Account,best32.));

art297
Opal | Level 21

Agreed!  Based on user notes like http://support.sas.com/kb/19/665.html I always presumed that it did do some kind of optimizing.  However, based on your post I tried to look it up and discovered that you are indeed correct .. as usual!

p.s. I've just started reading your book and, while I'm only at the beginning, I already like what I see!

Mr_Breeze
Calcite | Level 5

Art, that took care of it.

Thanks everyone for the help!!

Tom
Super User Tom
Super User

You should still double check what is causing the underlying issue.  If your account numbers have more than 12 significant digits or require leading zeros then you will not be able to convert the scientific notation generated by the BEST12. format back to the original string.

MikeZdeb
Rhodochrosite | Level 12

Hi ... here's something new in V9.3, creating an  INFORMAT (or FORMAT) from a user-defined function.

The MISSING and FIND in the SELECT statement is 'situation-specific' since this ...

when (^missing(input(a, best32.))) c = a;

currently does not work (produces errors).  Maybe someone else knows a good way to determine if a character string

can be converted to a number without using an INPUT function.

See also ... "Example 12: Creating a Function to Use as a Format"

http://support.sas.com/documentation/cdl/en/proc/63079/HTML/default/viewer.htm#p1gg77jyhc9s42n1f1vjy...


proc fcmp outlib=work.functions.math;

  function acc(a $) $;

  select;

    when (^missing(compress(a,,'d')) and ^find(a,'E+')) c = a;

    otherwise c = cats(input(a,best32.));

  end;

  return(c);

  endsub;

quit;

options cmplib=work.functions;

proc format;

invalue $acc other = [acc()];

run;

data new;

input x : & $acc32. @@;

datalines;

27569276   27569276   5.102E+11   LU520023190087756900  

8377715   11937232

1.0023E+11   4.07028E+19   662631175   1.53495E+11  

662631175   4127400093   ABC 123324

;

run;


results ...

27569276

27569276

510200000000

LU520023190087756900

8377715

11937232

100230000000

40702800000000000000

662631175

153495000000

662631175

4127400093

ABC 123324


Tom
Super User Tom
Super User

I do not have 9.3, but did you try adding '??' to the input function?

when (^missing(input(a, ??best32.))) c = a;

art297
Opal | Level 21

Tom,  That is what I did in my original suggestion.

MikeZdeb
Rhodochrosite | Level 12

Hi ... here's why ...

32   proc fcmp outlib=work.functions.math;

33     function acc(a $) $;

34     select;

35       when (^missing(input(a, ?? best32.))) c = a;

                                 -

                                 22

                                 200

ERROR 22-322: Expecting a format name.

ERROR 200-322: The symbol is not recognized and will be ignored.

36       otherwise c = cats(input(a,best32.));

37     end;

38     return(c);

39     endsub;

40   quit;


You get the same error with ...

when (^missing(input(a, ? best32.))) c = a;

So, after a few email exchanges, I learned that ? and ?? might be available in PROC FCMP in a later version of V9.3.

ps  Another variation ...

http://www.sascommunity.org/wiki/Tips:Create_an_Informat_from_a_User-Defined_Function

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
  • 14 replies
  • 7683 views
  • 0 likes
  • 6 in conversation