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

Reply
Occasional Contributor
Posts: 15

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

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?

Super User
Posts: 5,095

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

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.

Occasional Contributor
Posts: 15

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

Thanks, Aastounding.

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

Respected Advisor
Posts: 3,777

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

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
Occasional Contributor
Posts: 15

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

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!

Super User
Posts: 10,538

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

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.

Ask a Question
Discussion stats
  • 5 replies
  • 482 views
  • 0 likes
  • 4 in conversation