Unable to read dollar numeric value from CSV

[sing29]

I am new to SAS programming.

While attempting to load the data from CSV file I am unbale to load column values with dollar values

Make	Model	Type	Origin	DriveTrain	MSRP	Invoice
Acura	MDX	SUV	Asia	All	$36,945	$33,337

I am using the below code :

data work.new;
infile  '/home/singhthesantosh0/San_fo/cars.csv' dlm=',' firstobs=2;
input Make :$ Model :$ Type :$ Origin :$ DriveTrain    :$
    MSRP dollar9.2 Invoice dollar12.2;
run;
proc print data=work.new;
run;

I am getting below results:

Obs Make Model Type Origin DriveTrain MSRP Invoice 1 2

Acura	MDX	SUV	Asia	All	.	.
Acura	RSX Type	Sedan	Asia	Front	.	.

Could appreciate very much for the step I am missing to read the data

[Cynthia_sas · Accepted solution]

Hi, The COMMA informat will strip out all punctuation, including dollar signs.

  You have already used the colon format modifier with your other variables (where you do not need it for the character variables). So you only need to use the : with your numeric variables:

input Make $ Model $ Type $ Origin $ DriveTrain $ MSRP : comma. Invoice : comma.;

It is better to leave off the decimal places in an informat because the informat will detect whether there is already a decimal place when it is reading the data files. If you leave the .2 in the informat, that is telling the INPUT statement to insert a decimal place if it doesn't find one, which does not seem what you want to do given your data.

  Also you showed your INPUT data in a tabular form, but I would assume that the currency amounts are quoted, since they contain commas?

"Acura","MDX","SUV","Asia","All","$36,945","$33,337"

cynthia

[sing29]

Thanks Cyanthia,

I had applied the currency format in the CSV file due to which its value
had been converted to "" . So I selected number format in the CSV file and
now I can access the data.




##- Please type your reply above this line. Simple formatting, no
attachments. -##

[ballardw]

There is likely not a dollarw.d INFORMAT. Read them a character long enough to read the longest value.

Then to get the numeric version

Dollarnum =input(substr(Dollar,2),comma12.2));

You may want to use an Informat statement or set the length of the character variables before in the input statement if you find any of the longer text truncated.

[Tom]

You should probably use the DSD option on the INFILE statement to make sure it handles missing values and quoted strings properly.  You should also add the TRUNCOVER option just in case some of the lines do not contain all variables.

You should use the COMMA informat to read you values that contain commas and dollar signs.

Also is much easier if define your variables first before using them in the INPUT statement, rather than forcing SAS to guess how you want the variables defined based on how they are first referenced.

data work.new;
  infile  '/home/singhthesantosh0/San_fo/cars.csv' dsd dlm=',' firstobs=2 truncover;
  length Make Model Type Origin DriveTrain $20 MSRP Invoice 8;
  informat MSRP Invoice comma12. ;
  input Make Model Type Origin DriveTrain MSRP Invoice;
run;
proc print data=work.new;
run;