topic Reading csv with currency in data in SAS Programming

Reading csv with currency in data

BobyGadu — Tue, 19 Mar 2013 17:41:58 GMT

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,

Re: Reading csv with currency in data

AncaTilea — Tue, 19 Mar 2013 18:49:12 GMT

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.

Re: Reading csv with currency in data

BobyGadu — Tue, 19 Mar 2013 19:06:35 GMT

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

Re: Reading csv with currency in data

AncaTilea — Tue, 19 Mar 2013 19:11:19 GMT

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)

Re: Reading csv with currency in data

BobyGadu — Tue, 19 Mar 2013 19:38:14 GMT

Please try with txt with , delimiter

Re: Reading csv with currency in data

AncaTilea — Tue, 19 Mar 2013 19:42:22 GMT

I see. It doesn't work.

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

Thanks.

😐

Re: Reading csv with currency in data

BobyGadu — Tue, 19 Mar 2013 20:16:36 GMT

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

Re: Reading csv with currency in data

Tom — Tue, 19 Mar 2013 20:17:59 GMT

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.

Re: Reading csv with currency in data

art297 — Tue, 19 Mar 2013 20:20:46 GMT

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)=";";

end;

input id price label;

run;

Re: Reading csv with currency in data

BobyGadu — Tue, 19 Mar 2013 20:30:12 GMT

Awesome it worked

Re: Reading csv with currency in data

BobyGadu — Tue, 19 Mar 2013 20:30:31 GMT

Thanks a lot

Re: Reading csv with currency in data

BobyGadu — Tue, 26 Mar 2013 16:43:11 GMT

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

Re: Reading csv with currency in data

art297 — Tue, 26 Mar 2013 17:26:21 GMT

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

Re: Reading csv with currency in data

BobyGadu — Tue, 26 Mar 2013 17:35:20 GMT

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

Re: Reading csv with currency in data

art297 — Tue, 26 Mar 2013 18:21:33 GMT

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

input a b c amount d e f g h;

run;

Re: Reading csv with currency in data

BobyGadu — Tue, 26 Mar 2013 19:00:24 GMT

sorry about my data but why are you specifically doing for first 3 records and first 5 records

Re: Reading csv with currency in data

art297 — Tue, 26 Mar 2013 19:03:58 GMT

It not the first three (and five) records but, rather, the first three variables and the last five variables.

The code is changing the commas to semicolons for all fields except the fourth one, the one that can contain embedded commas.

Then it is simply reading the record, using semicolons as the delimiter and only expecting to possibly confront commas when reading the fourth field.

Re: Reading csv with currency in data

BobyGadu — Tue, 26 Mar 2013 19:07:19 GMT

Thank you its gr8 function I ever came across thank you very much to learn about this

Re: Reading csv with currency in data

data_null__ — Tue, 26 Mar 2013 19:36:48 GMT

data dollar;
   infile cards dsd column=col;
   input (a b c)($) @;
   call scan(_infile_,-5,p,l,',');
   l = p-col-1; 
   input amv $varying32. l +1 d $ e:percent. f $ g $ h :percent.; 
   cards; 
a,b,c,$200,x,0.0%,xy,sd,12.90%
z,c,d,$123,456,789,g,9.0%,aw,fr,78.78%
;;;;

Re: Reading csv with currency in data

RichardinOz — Wed, 27 Mar 2013 05:53:26 GMT

You may not have any control over the source of the CSV, but if you do and it comes out of Excel, you could request a tab delimited file as source instead. This is just as easy to produce from Excel, and can be read with the standard Proc Import in SAS. The advantages are: commas in the value are no longer a problem, no non standard code to maintain, and any additional columns which might be introduced into the data will be dealt with automatically.

I would also advocate use of the comma informat.

Richard