CODEWITHSUNDEEP
sasinformat
plainter
plainter

Reputation: 29

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

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！

Upvotes: 1

Views: 102

Answers (2)

Reeza
Reeza

Reputation: 21274

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;

Upvotes: 0

Dmitry.Kolosov
Dmitry.Kolosov

Reputation: 635

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;

Upvotes: 1

Related Questions