06-11-2015 10:59 AM
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?
06-11-2015 11:03 AM
What does your data look like exactly after import? I.e. give examples of what x looks like. I imagine that its imported as text, but is it actually looking like: 01-01-2015?
06-11-2015 01:05 PM
Perhaps the IF is not catching N/A. It may still be working for the good dates.
06-11-2015 01:49 PM
One other thing, you could have dates which are actually invalid - Excel does tend to hide things. Run your code, and look at the dataset created. Where clause off N/A fields and see if any "dates" are not coding correctly, 31/06/2015 for instance may look fine but is invalid.
06-11-2015 02:07 PM
Other obnoxious behaviors from Excel and people's data entry behaviors.
Some of the dates may have been entered as strings which would look like dates though invalid per RW9.
Also, if an entire column is not formatted as a date type is Excel then the export could get a mix of date values in the DDMMYYYY format and others as Excel date values such as 41234.
06-11-2015 02:58 PM
Not a fan of how SAS imports excel files either, as it's difficult to assign the format you want. Here's some code that will extract day, month, and year from the original value and create a new value that is in ddmmyyyy format and sets a missing value for any N/A.
input date $ 10.;
format day month 2. year 4. newdate ddmmyy10.;
If date="N/A" then do;
day=""; month=""; year=""; end;
day=scan(date,1,"/"); month=scan(date,2,"/"); year=scan(date,3,"/"); end;
If day="" then newdate=.;
drop day month year;
Hope that helps!
06-11-2015 04:55 PM
Not so much the SAS import but the actual storage in Excel due to user behaviors. Since a single column in Excel may contain formulae that yield numeric results, formulae that yield character results, entered numeric values, entered character values, mixtures of display characteristics such as some dates appearing as dd/mm/yyyy or mm/dd/yyyy or dd-Mon-yy, Month name day and year.
When those kinds of behaviors get mixed you get headaches trying to fix them.
06-12-2015 04:09 AM
Yes, its one of those things that non-technical people just don't understand. Its all well and good the management getting flashy colored spreadsheets and thinking they actually do something by pivoting it. The simple fact is though that Excel is not a data transfer format, or a database, or a data capture, or data analysis, or and IDE, or anything else. Its an unstructured, uncontrolled, mess of an application and really has no place on any computer.