import cv file with numeric comma separator decimal

[Nasser_DRMCP]

hello,

I have a csv file that holds rates values like 0,04715 or 0,0428. ',' is the decimal separator.

When I load the data by using proc import , the numbers are 4,715 and 428.

Maybe I should do this by using "data ...infile..." but I tried and I did not succed to get the data.

many thanks in advance for your help.

regards

Nasser

[ballardw]

It may help us if you open the CSV file with a text editor such as Notepad or even the SAS Editor  (NOT any spreadsheet) and then paste 2 or 3 lines of the data into a code box opened using the forums {I} menu icon. I recommend this box as the main message window will reformat text.

If there is any sensitive data within that example the replace values as appropriate such as names with XXXX or YYYY and any numeric with something of a similar pattern but with something generic.

[Nasser_DRMCP]

COL1;YEARMONTH;RATE
XXXX;201709;0,04715
XXXX;201710;0,04477
XXXX;201711;0,04477
XXXX;201712;0,04477
XXXX;201801;0,0428
XXXX;201802;0,0428
XXXX;201803;0,0428
XXXX;201804;0,0413

Thanks Ballardw. I copied 8 lines.

[Tom]

No need to use PROC IMPORT for a file with only three columns.

Use the COMMAX informat to let SAS know to swap the decimal and thousands separators.

data want ;
  infile 'myfile.txt' dsd dlm=';' truncover firstobs=2;
  input COL1 :$20. YEARMONTH_char :$6. RATE :commax. ;
  YEARMONTH= input(yearmonth_char||'01',yymmdd8.);
   format yearmonth yymmn6. ;
run;

[Nasser_DRMCP]

Thanks TOM

by using your proposed solution. I succeed to load the 2 first col but the rate is not loaded and I get a warning "

Invalid data for RATE

[Reeza]

Can you post the full error from the log, including the line it had the issue with?

[Tom · Accepted solution]

Check your end of line characters.

Read a few records from the file in fixed block mode and check what hex codes are at the end of the lines.

data _null_;
  infile 'myfile.txt' recfm=f lrecl=100 obs=3 ;
  input;
  list;
run;

It might be that you have '0D0A'x at the end of the line and SAS is assuming you are on Unix and expects only '0A'x to mark the end of the line. So the '0D'x becomes part of the data and is an invalid character for a numeric value.

Try adding TERMSTR=CRLF to your infile statement.

[Nasser_DRMCP]

Hello,

Thanks to all for your quick responses.

As suggested by Tom, by specifying TERMSTR=CRLF, then It works.

regards

Nasser

[Nasser_DRMCP]

Hello,

I have subsidiary question. I have 20 columns like RATE_1_... RATE_2_... RATE_3...RATE_20

Is it possible to specify :commax. for all columns begins with RATE_ ?

for a column that contains dates like 01/01/2015, I specify :ddmmyy10. but "20089" is loaded ! what is the adequat format ?

thanks for your help

Nasser

[Nasser_DRMCP]

Hello,

I succeed to load the date by specifying

format PERIODE ddmmyy10. ;

Nasser

[Nasser_DRMCP]

hello,

I found the solution for multple columns. the syntax in the input statement is below

(RATE_2007-RATE_2021) (15*:commax.)

Nasser

[Tom]

---

@Nasser_DRMCP wrote:

hello,

I found the solution for multple columns. the syntax in the input statement is below

(RATE_2007-RATE_2021) (15*:commax.)

Nasser

---

Right.  Note that you don't need the 15* as SAS will automatically recycle the list of informats (or formats) when it is shorter than the list of variables.  You could also remove the informat specifications from the INPUT statement and instead add an INFORMAT statement.

[Tom]

Depending on your LOCALE setting you might need to use COMMA informat instead of COMMAX.