How to format mix of dates and N/A in SAS date format

[Red_Squirrel]

o

Hello,

I have and Excel file that I'm reading into SAS.

One of the columns has got the mix of dates and N/A values.

I'm trying to write this into SAS format of ddmmyy10.0.

That's  what I have so far in my code:

if x = 'N/A' then y = .;

else y = input (strip(x), ddmmyy10.0);

format y ddmmyy10.0;

this returns me the error message:

Invalid argument to function INPUT at line....

can can you please advise what needs to be done to correct the issue?

[Astounding]

You have an extra "0" in your code.  10.0 is a valid format/informat to indicate one position after the decimal point.  But dates don't have positions after the decimal point, so switch to ddmmyy10. instead of ddmmyy10.0.

Good luck.

[Red_Squirrel]

Thanks, Aastounding.

i Changed this previously as well however I still get the same error message.

[data_null__]

I don't think the .0 is the problem.

data _null_;
   x = input('10-10-15',ddmmyy10.0);
   put x=date9.;
   run;

23         data _null_;
24            x = input('10-10-15',ddmmyy10.0);
25            put x=date9.;
26            run;

x=10OCT2015
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[Red_Squirrel]

Thank You all for the valuable contributions.

None of the suggestions helped unfortunately.

I think that's due to the very poor quality level of data in the Excel file.

As I am not able to influence production of this file I decided to chang the code logic and get rid of this particular data transformation altogether.

Thank you!

[ballardw]

Also look at the values of X. Depending on how you moved the data from Excel to SAS you may have values like "41950" which would not be valid for reading using almost any informat available. Search the formun for Excel date values and you'll find several discussions on this topic.

Unless you have a whole bunch of files to process I would be very tempted to go back and save the Excel to CSV format and then import that. You may have better luck as you can modify Proc Import generated code to set variable informats and have better control. Reading the date values with a date informat will then turn any of the N/A values to missing at read.