topic Re: Problem with format when loading from CSV file in SAS Programming

Problem with format when loading from CSV file

imdickson — Wed, 13 Jun 2018 04:03:46 GMT

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?

Re: Problem with format when loading from CSV file

Tom — Wed, 13 Jun 2018 04:51:53 GMT

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.

Re: Problem with format when loading from CSV file

ChrisNZ — Wed, 13 Jun 2018 04:52:52 GMT

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

Re: Problem with format when loading from CSV file

imdickson — Mon, 18 Jun 2018 03:32:08 GMT

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.

Re: Problem with format when loading from CSV file

ChrisNZ — Mon, 18 Jun 2018 03:36:46 GMT

Add a format for your variable.
format VAR comma20.2;

Re: Problem with format when loading from CSV file

Tom — Mon, 18 Jun 2018 04:09:35 GMT

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