BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sing29
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5
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
Super User

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6593 views
  • 2 likes
  • 4 in conversation