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.
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.
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.
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.
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?
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!
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.
Ready to level-up your skills? Choose your own adventure.