SAS importing wrong date

[UcheOkoro]

Hello, 

I am importing data from an excel csv file but noticed that out of 1279 observations, two dates are wrong. The excel file has the correct date but SAS has the wrong date. I don't know.

Please, I need some help. 

Below are the SAS output and snip of excel sheet.

[Reeza]

Do you want to add some context to this?
Did you try and convert the dates and that went wrong?
How did you import the data? Did you specify the correct informat?
If you used proc import or one of the automated methods, that's why you have this issue. In this case, it's better to write a data step that allows you to specify your informats and then you don't have this issue. IMPORT procedures are guessing procedures...and well, guesses are sometimes wrong.

[UcheOkoro]

Hello Reeza,

Thank you for  your prompt response. I use proc import

below. I did specify my informat or did I do it wrong?

Thank you again

proc import datafile="R:\Research\x\data_new.csv" out=data_new2
               DBMS=csv REPLACE;
              guessingrows=5000;
run;

   informat sirs_date mmddyy10. ;
 informat triage_date mmddyy10. ;
 format triage_date mmddyy10. ;
 format sirs_date mmddyy10. ;

[Reeza]

PROC IMPORT doesn't allow you to specify an informat/format, are those the ones it used in the log?

[Reeza]

It's hard to follow the pictures in your post....so I'm not sure if you're reading a date from the wrong column there as I see both dates.
I would also recommend checking the CSV file in a text editor, not Excel. Excel can misinterpret or misdisplay your data, and in fact I've seen instances where the CSV has the date as yymmdd and Excel displays it as MMDDYY so you cannot rely on Excel for the data format.

[ballardw]

---

@UcheOkoro wrote:

Hello Reeza,

Thank you for  your prompt response. I use proc import

below. I did specify my informat or did I do it wrong?

Thank you again

proc import datafile="R:\Research\x\data_new.csv" out=data_new2
               DBMS=csv REPLACE;
              guessingrows=5000;
run;

   informat sirs_date mmddyy10. ;
 informat triage_date mmddyy10. ;
 format triage_date mmddyy10. ;
 format sirs_date mmddyy10. ;

---

You don't get to specify a format or informat for Proc Import. If that is the code you submitted there are likely a number of error messages.

Proc Import makes (educated) guesses based on the content of a file.

Proc import creates data step syntax to read the file. You could copy the syntax generated from the log, paste into the code editor, clean it up and specify the informat in the data step.

One strongly suspects you are showing us a picture of something in Excel. That is extremely unreliable as it will interpret data and can change values. Just last week I downloaded some data from the US Census department with a header that was "1-21" for an AGE variable. Opening the file in Excel shows a column heading of "Jan 21".

You want to examine CSV files with a text editor if have questions about the values and why SAS may have read them one way.

I am afraid that your pictures without a lot of words do not explain anything.

Suggestion if the data is not sensitive: Open the CSV file (assuming you did not SAVE it from Excel, in which case the file is likely now corrupt) with a text reader like NOTEPAD or similar. Copy that line with the "error" and paste it into a text box opened on the forum with the </> icon. The text box is to preserve text as is. The message windows on this forum will reformat text and may remove something.

Then tell us which column is supposed to have the date and what you think the date should be and what the corresponding date SAS create is. I am afraid your pictures do not relate this very clearly and there is nothing that is the actual CSV file read.

Typically when most of the values are as expected with a couple of oddballs, especially if they are dates, it indicates that something entered for those few values are appear differently than the majority of values.