topic Re: Date11. Format Issue while reading the raw data in SAS in SAS Programming

Date11. Format Issue while reading the raw data in SAS

satyamsachan26 — Wed, 15 Jun 2022 13:26:07 GMT

Hi All,

I am trying to read the below data but I think because of date11. format, data is not correctly read by SAS. Please help how to read this data.

data transaction;
infile cards dlm='09'x;
input Customer_Id Trans_Id Trans_Date date11. Trans_Amt 5.0;
format Trans_Date date9.;
cards;
101 111 21-Apr-21 20000
102 167 22-Apr-22 15000
104 345 23-Apr-22 14000
105 112 24-Apr-22 12000
run;

Data is tab-delimited to I used dlm='09'x.

O/P-

Trans_Amt is not correct.

Re: Date11. Format Issue while reading the raw data in SAS

PaigeMiller — Wed, 15 Jun 2022 13:30:44 GMT

input Customer_Id Trans_Id Trans_Date :date11. Trans_Amt 5.0;

Note the colon (not semi-colon) before date11.

The colon indicates that SAS should start reading from the next non-blank column until it reaches another non-blank column.

Date11. is an INFORMAT, it is used when reading data IN. In this case, DATE11. is not a FORMAT.

Re: Date11. Format Issue while reading the raw data in SAS

Astounding — Wed, 15 Jun 2022 15:22:08 GMT

While @PaigeMiller has given you the right solution, it might be important to you to understand why your original program is not working.

The DATE11. informat tells SAS to read 11 characters. That includes the first digit of the Trans_Amt (not a good idea). While SAS figures out to ignore that digit when assigning a value to your date variable, it still is in the wrong position to accurately read Trans_Amt. You can see that your Trans_Amt values begin with the second digit, not the first digit, of the proper amount.

Re: Date11. Format Issue while reading the raw data in SAS

Tom — Wed, 15 Jun 2022 15:31:39 GMT

Do not read a delimited file using formatted input statement. The fixed length of the informat specification in the INPUT statement will ignore the delimiters. So if the width of the field in the line is different than the width in the informat specification the pointer will be in the wrong place to read the next field on the line.

You can add an informat to the INPUT statement and still use list mode by using the colon modifier. Also when you use list mode to read the value SAS will IGNORE the width on the informat, instead it will adjust to the width of the field being read. Your last variable does not need any special informat since it is just a number. SAS already knows how to read numbers.

input Customer_Id Trans_Id Trans_Date :date. Trans_Amt ;

Also do you really want to make CUSTOMER_ID and TRANS_ID as NUMERIC variables? What would be the meaning of the mean or standard deviation of a CUSTOMER_ID?

Re: Date11. Format Issue while reading the raw data in SAS

satyamsachan26 — Wed, 15 Jun 2022 15:38:02 GMT

Thanks a lot for this quick solution Paige Miller.

Re: Date11. Format Issue while reading the raw data in SAS

satyamsachan26 — Wed, 15 Jun 2022 15:39:04 GMT

Hi, Thanks a lot for this detailed explanation.

Re: Date11. Format Issue while reading the raw data in SAS

satyamsachan26 — Wed, 15 Jun 2022 15:45:36 GMT

Got your point Tom, No need to make Customer_id and TRANS_ID as numeric here.
Also thanks a lot for this detailed solution.