Reading csv with currency in data

Reply
Contributor
Posts: 30

Reading csv with currency in data


Need to read this data from CSV problem here is while reading as dlm =',' after $ amount it is delimiting how to read it tried with dollar8. and comma formats but no luck

1,$2,000,abc,

2,$5,00,000,xyz,

Super Contributor
Posts: 543

Re: Reading csv with currency in data

How about this?

data want;
  infile 'your_path_to_csv_file.csv' delimiter = ', '
     MISSOVER DSD lrecl=32767 firstobs=2 ;
     informat id best32. price dollar20.0 label $5. ;
     format id best12.   price dollar20.0 label $5. ;
  input id price label;
  run;

Let us know if this helps.

Good luck,

Anca.

Contributor
Posts: 30

Re: Reading csv with currency in data

Hi Anca thank you for your reply but for price comma it is taking as delimited record cannot read reading into other field

Super Contributor
Posts: 543

Re: Reading csv with currency in data

Hmmm, I am reading a csv file with dollar amounts that include commas and it worked....(see the two attached files with csv and sas result)

Smiley Sad


currency.bmpsas_currency.bmp
Contributor
Posts: 30

Re: Reading csv with currency in data

Please try with txt with , delimiter

Super Contributor
Posts: 543

Re: Reading csv with currency in data

I see. It doesn't work.

Can you open/save as your file as .CSV then run my initial code?

Thanks.

Smiley Indifferent

Contributor
Posts: 30

Re: Reading csv with currency in data

data is not in seperate cells in single column the data is seperated by ', ' I tried with csv

Super User
Super User
Posts: 6,502

Re: Reading csv with currency in data

Best solution would be to fix the problem at the source.  Who ever generated that file should have put quotes around the values that included commas.

1,"$2,000",abc,

2,"$5,00,000",xyz,

Can you fix the program that generates the CSV file?

If not the you need a strategy to fix the existing file.  You could look for a regular expression solution that could find match strings that look like dollar amounts and insert the quotes for you.

If your data is as simple as the example where there is only one field that has dollar amounts then you could use SCAN functions to parse the input record.  So you could scan from the left and from the right and whatever is left over is your dollar amount.

PROC Star
Posts: 7,364

Re: Reading csv with currency in data

Hopefully, your example data's 2nd record was wrong and it was really supposed to contain $500,000

If so, and only have the 3 variables, and each line ends with a comma, you might be able to get away with:

data want;

  infile 'your_path_to_csv_file.csv' delimiter = ';'

    MISSOVER DSD lrecl=32767 firstobs=1 ;

      informat id best32. price dollar20.0 label $5. ;

      format id best12.   price dollar20.0 label $5. ;

  input @;

  if count(_infile_,",") eq 3 then do;

    _infile_=translate(_infile_,";",",");

  end;

  else do;

    substr(_infile_,find(_infile_,",",1),1)=";";

    substr(_infile_,find(_infile_,",",-32767),1)=";";

    substr(_infile_,find(_infile_,",",-32767),1)=";";

  end;

  input id price label;

run;

Contributor
Posts: 30

Re: Reading csv with currency in data

Awesome it worked

Contributor
Posts: 30

Re: Reading csv with currency in data

Thanks a lot

Contributor
Posts: 30

Re: Reading csv with currency in data

had more fields

a,b,c,$amount,d,e,f,g,h,i

these many variables not just 3 how to handle these with these many variables please i didnt understood the _infile_ logic applied if it is having 3 variables are we taking count =3 or else any other logic and if variables increase means do we need to add mor substr logics

PROC Star
Posts: 7,364

Re: Reading csv with currency in data

: More info is needed in order to answer your question.  In your original example, only the 2nd field had data which could contain an embedded comma.  Do any of your additional fields have data that could contain embedded commas.

_infile_, a system variable, is created the first time an input statement is encountered during an interatioin.  In the suggested code, an "input @;" statement is used to create _infile_, then _infile_ is modified to account for the possible embedded commas, and then a 2nd input statement is used to actually obtain the data from the modified _infile_ variable.

Contributor
Posts: 30

Re: Reading csv with currency in data

a,b,c,$amount,d,e,f,g,h,i

a,b,c,$200,x,0.0%,xy,2 ab,sd,12.90%

z,c,d,$123,456,789,g,9.0%,aw,fr,78.78%

data looks like this

PROC Star
Posts: 7,364

Re: Reading csv with currency in data

: You would really save yourself, and everyone here, quite a bit of time if you checked your posts a little more before sending.  You indicate having 10 fields, but only show nine in your data.  The following assumes that you only have the nine fields shown in your data and that, for record 1, "2 ab" was really supposed to be only 'ab'.  If you really have any space embedded data, you will have to add an & modifier in the input statement for those fields.

data want;

  infile 'c:\art\your_path_to_csv_file.csv' delimiter = ';'

    MISSOVER DSD lrecl=32767 firstobs=1 ;

      informat amount dollar20.0

               a b c d f g$5.

               e h percent12.2;

      format e h percent12.2;

      format amount dollar20.0;

  input @;

  if count(_infile_,",") eq 9 then do;

    _infile_=translate(_infile_,";",",");

  end;

  else do;

    do _n_=1 to 3;

      substr(_infile_,find(_infile_,",",1),1)=";";

    end;

    do _n_ = 1 to 5;

      substr(_infile_,find(_infile_,",",-32767),1)=";";

    end;

  end;

  input a b c amount d e f g h;

run;

Ask a Question
Discussion stats
  • 19 replies
  • 1546 views
  • 5 likes
  • 6 in conversation