I'm a new user of SAS so there is likely to be a very straightforward answer to my question. i have a dataset which has dates of diagnosis in the format DDMMYY. i'm trying to replace some implausible dates, so those observations that occurred at a time that was impossible, and replace them with a missing value ("."). Can anybody let me know the statements that would do this for me.
i have tried IF THEN, but it hasn't seemed to have worked...
First, make sure that the dates are stored internally as SAS dates. You can do this by checking the PROC CONTENTS output and looking at the data type (numeric) and display format (mmddyy.). If the contents show that the field is a character field, then you have more work to do.
IF DateDx > today() THEN datedx=.;
would change any future date to missing. You can change the expression after the IF to what you need.
If the date is actually a character string, then you first need to convert it to a date data type to do the comparison. For instance,
IF (INPUT(DateDx,mmddyy8.) > today() ) THEN DateDx=' ';
would accomplish a similar task. You may want to convert the text-date permanently to date data types.
One last thing. If your data are coming from a relational database (e.g. Oracle, SQL/Server), then the underlying field is a date-time, even though it may display as a date. You will need to either extract the date part (DATEPART is the function name) or adjust the comparisons to account for the different scaling.
The underlying field was indeed date-time so I have extracted the date part. However, I am trying to replace all those observations that occurred before 01/02/1920. Though, instead it deletes those before 01/01/1960. I am aware that this is the base date, but don't know how to get round the problem.
It would be best to share exact SAS code being executed, but do so as part of a COPY/PASTE reply to your post with the SAS-generated log, not just your code piece.
Suggest you incorporate some SAS diagnostic command logic, like below, at various points in your DATA step logic flow to display data values -- also considering how you are coding any SAS DATE constants (with or without the year/century), as was mentioned previously:
Most important, you need to share your SAS-generated log output with all code revealed to get further guidance, other than someone's guess.