Reading csv with currency in data

[BobyGadu]

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,

[AncaTilea]

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.

[BobyGadu]

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

[AncaTilea]

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)

[BobyGadu]

Please try with txt with , delimiter

[AncaTilea]

I see. It doesn't work.

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

Thanks.

😐

[BobyGadu]

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

[Tom]

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.

[art297]

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;

[BobyGadu]

Awesome it worked

[BobyGadu]

Thanks a lot

[BobyGadu]

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

[art297]

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

[BobyGadu]

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

[art297]

: 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;