topic Re: Separating numbers in an input file by commas in SAS Programming

Separating numbers in an input file by commas

YangM90 — Tue, 16 Jun 2020 08:42:37 GMT

Hello SAS Forum,

I have an excel data file, where the columns are full of numbers. The numbers are seperated by columns; e.g. 1,356,292 (meaning approximately 4/3). But when I import the file via proc import:

proc import datafile = data
  out=data
  dbms=xlsx
  replace;
run;

SAS reads this as 1356292.

Is there an easy step to convert it "back" to the original format? 🙂

Best regards,

Re: Separating numbers in an input file by commas

Kurt_Bremser — Tue, 16 Jun 2020 09:13:47 GMT

That number can never be equivalent to ~1.3. It was, is, and will always be 1356292. You cannot have more than one decimal point (either represented by the period or the comma, depending on locale) in a number.

Otherwise, how could you know it's not 1356.292?

Re: Separating numbers in an input file by commas

andreas_lds — Tue, 16 Jun 2020 09:13:49 GMT

The problem is, that "1,356,292" is only a valid number if the comma is treated as thousands-separator, which is exactly what SAS does. If the data would be stored in a proper file-format for holding data (like csv), you could write code to fix the strange format, but with excel post-processing the data is the only option you have. And for that rules must exists defining the meaning of the comma at its various positions.

Re: Separating numbers in an input file by commas

Patrick — Tue, 16 Jun 2020 10:41:31 GMT

Even with Excel there is a difference between how a number "prints" formatted and what's stored internally.

SAS reads the internal value and then applies a standard SAS format for printing.

You can always change the format also in SAS.

On-the-fly within a single Procedure

data test;
  myvar=1356292;
  output;
  stop;
run;

proc print data=test;
  format myvar comma16.;
  var myvar;
run;

Or permanently attached to the variable in the table:

proc datasets lib=work nolist;
  modify test;
    format myvar comma16.;
  run;
quit;

proc print data=test;
run;

Re: Separating numbers in an input file by commas

Reeza — Tue, 16 Jun 2020 16:34:08 GMT

The numbers are seperated by columns; e.g. 1,356,292 (meaning approximately 4/3). But when I import the file via proc import:

Excel is storing that as one million three hundred fifty six thousand two hundred ninety two, not 4/3.

If that value is actually comma separated numbers then you'll need to format that column as text within Excel.

Unfortunately when reading from Excel it's very difficult to force the types when importing data, if you're reading from a different source such as CSV, XML or JSON.