BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
satyamsachan26
Fluorite | Level 6

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-

satyamsachan26_1-1655299268928.png

Trans_Amt is not correct.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller
Astounding
PROC Star

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.

satyamsachan26
Fluorite | Level 6
Hi, Thanks a lot for this detailed explanation.
satyamsachan26
Fluorite | Level 6
Thanks a lot for this quick solution Paige Miller.
Tom
Super User Tom
Super User

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?

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

hackathon24-white-horiz.png

Join the 2025 SAS Hackathon!

Calling all data scientists and open-source enthusiasts! Want to solve real problems that impact your company or the world? Register to hack by August 31st!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1362 views
  • 5 likes
  • 4 in conversation