DATA Step, Macro, Functions and more

import cv file with numeric comma separator decimal

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

import cv file with numeric comma separator decimal

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

 


Accepted Solutions
Solution
‎03-19-2018 04:14 AM
Super User
Super User
Posts: 7,938

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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.

View solution in original post


All Replies
Super User
Posts: 13,347

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 117

Re: import cv file with numeric comma separator decimal

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.

 

Super User
Super User
Posts: 7,938

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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;
Frequent Contributor
Posts: 117

Re: import cv file with numeric comma separator decimal

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

Super User
Posts: 23,332

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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

Solution
‎03-19-2018 04:14 AM
Super User
Super User
Posts: 7,938

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 117

Re: import cv file with numeric comma separator decimal

Hello,

 

Thanks to all for your quick responses.

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

regards

Nasser

Frequent Contributor
Posts: 117

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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

Frequent Contributor
Posts: 117

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

Hello,

 

I succeed to load the date by specifying

format PERIODE ddmmyy10. ;

 

Nasser

Frequent Contributor
Posts: 117

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

hello,

 

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

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

 

Nasser

Super User
Super User
Posts: 7,938

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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

Super User
Super User
Posts: 7,938

Re: import cv file with numeric comma separator decimal

Posted in reply to Nasser_alfea

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 210 views
  • 4 likes
  • 4 in conversation