Help using Base SAS procedures

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

Reply
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
Super User
Posts: 7,988

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

Posted in reply to Red_Squirrel

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: 11,343

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

Posted in reply to Red_Squirrel

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

Respected Advisor
Posts: 3,799

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

Posted in reply to Red_Squirrel

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)Smiley SadColumn)
.
     
1 at 25:11  
Super User
Super User
Posts: 7,988

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

Posted in reply to Red_Squirrel

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: 11,343

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

Posted in reply to Red_Squirrel

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: 130

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

Posted in reply to Red_Squirrel

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: 11,343

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
Super User
Posts: 7,988

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. 

Ask a Question
Discussion stats
  • 9 replies
  • 946 views
  • 0 likes
  • 5 in conversation