BookmarkSubscribeRSS Feed
imdickson
Quartz | Level 8

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?

 

 

 

5 REPLIES 5
Tom
Super User Tom
Super User

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.

imdickson
Quartz | Level 8

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.

ChrisNZ
Tourmaline | Level 20
Add a format for your variable.
format VAR comma20.2;
Tom
Super User Tom
Super User

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

ChrisNZ
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 5 replies
  • 2338 views
  • 0 likes
  • 3 in conversation