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

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

Red_Squirrel — Thu, 11 Jun 2015 14:59:06 GMT

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?

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

RW9 — Thu, 11 Jun 2015 15:03:00 GMT

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?

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

Red_Squirrel — Thu, 11 Jun 2015 15:06:33 GMT

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

16/04/2015

N/A

21/05/2015

01/06/2015

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

ballardw — Thu, 11 Jun 2015 16:47:39 GMT

As in your OTHER thread, DDMMYY10.0 should be DDMMYY10.

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

data_null__ — Thu, 11 Jun 2015 17:05:06 GMT

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   

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

RW9 — Thu, 11 Jun 2015 17:49:46 GMT

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.

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

ballardw — Thu, 11 Jun 2015 18:07:57 GMT

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.

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

dcruik — Thu, 11 Jun 2015 18:58:19 GMT

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!

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

ballardw — Thu, 11 Jun 2015 20:55:31 GMT

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.

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

RW9 — Fri, 12 Jun 2015 08:09:15 GMT

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.