DATA Step, Macro, Functions and more

Unable to read dollar numeric value from CSV

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

Unable to read dollar numeric value from CSV

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

MakeModelTypeOriginDriveTrainMSRPInvoice
AcuraMDXSUVAsiaAll$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

AcuraMDXSUVAsiaAll..
AcuraRSX TypeSedanAsiaFront..

 

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


Accepted Solutions
Solution
‎05-29-2016 12:07 PM
SAS Super FREQ
Posts: 8,863

Re: Unable to read dollar numeric value from CSV

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

View solution in original post


All Replies
Solution
‎05-29-2016 12:07 PM
SAS Super FREQ
Posts: 8,863

Re: Unable to read dollar numeric value from CSV

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

Regular Learner
Posts: 1

Re: Unable to read dollar numeric value from CSV

Posted in reply to Cynthia_sas
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. -##
Super User
Posts: 11,343

Re: Unable to read dollar numeric value from CSV

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.

 

 

 

Super User
Super User
Posts: 7,039

Re: Unable to read dollar numeric value from CSV

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;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 605 views
  • 0 likes
  • 4 in conversation