## How to convert mix of dates and N/A into SAS date format

Occasional Contributor
Posts: 15

# How to convert mix of dates and N/A into SAS date format

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: 9,599

## Re: How to convert mix of dates and N/A into SAS date format

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?

Occasional Contributor
Posts: 15

## Re: How to convert mix of dates and N/A into SAS date format

it is formatted as TEXT after import and looks this way:

16/04/2015

N/A

21/05/2015

01/06/2015

Super User
Posts: 13,523

Posts: 3,852

## Re: How to convert mix of dates and N/A into SAS date format

Perhaps the IF is not catching N/A.  It may still be working for the good dates.

data _null_;

do d = '21/05/2015','N/A','21/05/2015';

x = input(strip(strip(d)),ddmmyy10.0);
put x=date9.;

end;

run;

23         data _null_;
24            do d = '21/05/2015','N/A','21/05/2015';
25               x = input(strip(strip(d)),ddmmyy10.0);
26               put x=date9.;
27               end;
28            run;

x=21MAY2015
NOTE: Invalid argument to function INPUT at line
25 column 11.
x=
.
x=
21MAY2015
d=
21/05/2015 x=20229 _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following
places. The results of the operations have been set to
missing
values.
Each place is given by: (Number of times) at (Line)Column)
.

1 at 25:11
Super User
Posts: 9,599

## Re: How to convert mix of dates and N/A into SAS date format

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.

Super User
Posts: 13,523

## Re: How to convert mix of dates and N/A into SAS date format

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.

Frequent Contributor
Posts: 132

## Re: How to convert mix of dates and N/A into SAS date format

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.

data have;

input date \$ 10.;

datalines;

16/04/2015

N/A

21/05/2015

01/06/2015

;

run;

data want;

set have;

format day month 2. year 4. newdate ddmmyy10.;

If date="N/A" then do;

day=""; month=""; year=""; end;

Else do;

day=scan(date,1,"/"); month=scan(date,2,"/"); year=scan(date,3,"/"); end;

If day="" then newdate=.;

Else newdate=input(catx("/",day,month,year),ddmmyy10.);

drop day month year;

run;

Hope that helps!

Super User
Posts: 13,523

## Re: How to convert mix of dates and N/A into SAS date format

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.

Super User
Posts: 9,599

## Re: How to convert mix of dates and N/A into SAS date format

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.

Discussion stats
• 9 replies
• 1099 views
• 0 likes
• 5 in conversation