How to read informats data: $1,000.1M to 1000.1

Question

The datasets include a list of numbers:

    $1,000.1M
      $100.5M
    $1,002.3M
     $23.4M
     $120.3M

I want to read the variable as a numeric in SAS

the result should be:

   Money(millions)
   1000.1
   100.5
   1002.3
   23.4
   120.3

I used COMMAw.d to read this data, but cannot run

The code is:

    input Money(millions) COMMA9.1;
    run;

How to modify it?

Thank you very much！

Are all numbers millions? Or is it possible to have a B or to have numbers that are not in the millions? — Reeza, Feb 04 '18 at 01:27

score 1 · Accepted Answer · answered Feb 03 '18 at 20:37

The COMMA informat does not expect letters like 'M', it removes only commas, blanks, dollar signs, percent signs, dashes, and close parentheses. You can just convert your raw string to a string containing a number by removing all characters you do not need:

data input;
    length moneyRaw $200;    
    infile datalines;
    input moneyRaw $;

    datalines;
$1,000.1M
$100.5M
$1,002.3M
$23.4M
$120.3M
;
run;

data result;
    set input;
   * "k" modifier inverts the removed characters;
    money = input(compress(moneyRaw,"0123456789.","k"),best.);
run;

Or if you know regex, you can add some intrigue to the code for anyone who reads it in the future:

data resultPrx;
    set input;
    moneyUpdated = prxChange("s/^\$(\d+(,\d+)*(\.\d+)?)M$/$1/",1,strip(moneyRaw));
    money = input(compress(moneyUpdated,','),best.);
run;

I didn't know the compress function before, this is my first time to study SAS. Thank you for your help! — plainter, Feb 05 '18 at 23:34

score 0 · Answer 2 · answered Feb 05 '18 at 20:35

I think you're best off reading it as a character and then processing it as in Dmitry's answer. But if it was a single column you could read it if you set the delimiter to M. I suspect this will work in a demo, but not in your full process.

data input;
    informat moneyRaw dollar8.;    
    infile datalines dlm='M';
    input moneyRaw ;
    *moneyRaw = moneyRaw * (1000000);
    format moneyRaw dollar32.;
    datalines;
$1,000.1M
$100.5M
$1,002.3M
$23.4M
$120.3M
;
run;

Thank you! So is there no way to directly read this data as a numeric? — plainter, Feb 05 '18 at 23:37

How to read informats data: $1,000.1M to 1000.1

2 Answers2