topic Re: import cv file with numeric comma separator decimal in SAS Programming

import cv file with numeric comma separator decimal

Nasser_DRMCP — Fri, 16 Mar 2018 16:02:31 GMT

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

Re: import cv file with numeric comma separator decimal

ballardw — Fri, 16 Mar 2018 16:14:07 GMT

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.

Re: import cv file with numeric comma separator decimal

Nasser_DRMCP — Fri, 16 Mar 2018 16:20:09 GMT

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.

Re: import cv file with numeric comma separator decimal

Tom — Fri, 16 Mar 2018 16:39:39 GMT

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;

Re: import cv file with numeric comma separator decimal

Nasser_DRMCP — Fri, 16 Mar 2018 17:01:03 GMT

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

Re: import cv file with numeric comma separator decimal

Reeza — Fri, 16 Mar 2018 17:10:11 GMT

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

Re: import cv file with numeric comma separator decimal

Tom — Fri, 16 Mar 2018 17:12:34 GMT

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.

Re: import cv file with numeric comma separator decimal

Tom — Fri, 16 Mar 2018 17:55:39 GMT

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

Re: import cv file with numeric comma separator decimal

Nasser_DRMCP — Mon, 19 Mar 2018 08:14:35 GMT

Hello,

Thanks to all for your quick responses.

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

regards

Nasser

Re: import cv file with numeric comma separator decimal

Nasser_DRMCP — Mon, 19 Mar 2018 08:44:52 GMT

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

Re: import cv file with numeric comma separator decimal

Nasser_DRMCP — Mon, 19 Mar 2018 08:55:01 GMT

Hello,

I succeed to load the date by specifying

format PERIODE ddmmyy10. ;

Nasser

Re: import cv file with numeric comma separator decimal

Nasser_DRMCP — Mon, 19 Mar 2018 10:10:19 GMT

hello,

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

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

Nasser

Re: import cv file with numeric comma separator decimal

Tom — Mon, 19 Mar 2018 13:31:47 GMT

@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.