Problem with format when loading from CSV file

Reply
Frequent Contributor
Posts: 94

Problem with format when loading from CSV file

Good day everyone.

 

I am trying to load a csv file by using SAS Data Integration Studio with File Reader.

 

Here is the thing. The raw data looks something like this:

4048,3.18,USD,3.18,18-Dec-2008,3
4048,102,CAD,102,12-Jan-2009,102

The columns are:

StaffID Character 4

BankAmt Numeric Informat-Comma10.2

Currency Character 3

WalletAmt Numeric Informat-Comma10.2

Date DATE9.

Tips Numeric Informat-Comma10.2

 

However, when i load it using the above Informat, Tips for the first record became 0.03 instead of 3.

 

Does anyone know why?

 

 

 

Super User
Super User
Posts: 8,093

Re: Problem with format when loading from CSV file

[ Edited ]
Posted in reply to imdickson

Because that is what you told it to do.

 

When you specify a decimal part on an INFORMAT you are telling SAS where to place an implied decimal point when the text being read does not include a decimal point. So by use 10.2 as in the informat a value with only one digit, like 3 is taken to mean 0.03.  And a value with three digits without an period, like 102 means 1.02.  When the value already has a period then one is not implied, so a value like 12.34 is read as 12.34.

 

Just use an INFORMAT of 10.  or better still leave it blank if the tool will let you.  SAS already knows how to read numbers from text files so you don't need to override its default behavior by telling it to use some special instructions.

Frequent Contributor
Posts: 94

Re: Problem with format when loading from CSV file

Thanks @Tom and @ChrisNZ.

 

I changed the number formatting to remove comma in CSV file.

When I load it into SAS Dataset, there is no more comma for 1000 onwards.

 

HOWEVER, if i add comma back to 1000 in CSV, SAS will never be able to read it if i don't put COMMA10.2.

By using COMMA10., SAS ignores all my decimal value.

 

I need help if my CSV number doesnt have comma, how do i add comma back?

What if my CSV number contains comma, how do i make sure SAS read it properly.

PROC Star
Posts: 2,350

Re: Problem with format when loading from CSV file

Posted in reply to imdickson
Add a format for your variable.
format VAR comma20.2;
Super User
Super User
Posts: 8,093

Re: Problem with format when loading from CSV file

Posted in reply to imdickson

@imdickson wrote:

Thanks @Tom and @ChrisNZ.

 

I changed the number formatting to remove comma in CSV file.

When I load it into SAS Dataset, there is no more comma for 1000 onwards.

 

HOWEVER, if i add comma back to 1000 in CSV, SAS will never be able to read it if i don't put COMMA10.2.

By using COMMA10., SAS ignores all my decimal value.

 

I need help if my CSV number doesnt have comma, how do i add comma back?

What if my CSV number contains comma, how do i make sure SAS read it properly.


You seem confused about the difference between an INFORMAT and a FORMAT.

 

A FORMAT is instructions for how to convert values into text. So how you want the values to print. An INFORMAT is instructions for how to convert text into values. So how you want interpret values read from a text file.

 

If you want the value to display with commas you need to use COMMA format.  If you want it to display digits to the right of the decimal place then include the number of digits you want in the format specifications. 

 

But do NOT include a number of decimal places when using an INFORMAT, unless you purposely multiplied the value by that power of ten before writing it to the text file so that you did not need to write the period into the text file.

PROC Star
Posts: 2,350

Re: Problem with format when loading from CSV file

Posted in reply to imdickson

SAS expects two decimals.

This works:

data _null_;
  file "%sysfunc(pathname(WORK))\t.txt";
  put '4048,3.18,USD,3.18,18-Dec-2008,3';
  put '4048,102,CAD,102,12-Jan-2009,102';
run;

data WANT;
  infile "%sysfunc(pathname(WORK))\t.txt" dlm=',';
  informat STAFFID   $4.
           BANKAMT   comma20.
           CURRENCY  $3.
           WALLETAMT comma20.
           DATE      date9.
           TIPS      comma20.;
  input STAFFID            
        BANKAMT 
        CURRENCY 
        WALLETAMT 
        DATE 
        TIPS   ; 
run;

 

STAFFID BANKAMT CURRENCY WALLETAMT DATE TIPS
4048 3.18 USD 3.18 17884 3
4048 102.00 CAD 102.00 17909 102
Ask a Question
Discussion stats
  • 5 replies
  • 134 views
  • 0 likes
  • 3 in conversation