BookmarkSubscribeRSS Feed
Red_Squirrel
Calcite | Level 5

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?

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Red_Squirrel
Calcite | Level 5

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

16/04/2015

N/A

21/05/2015

01/06/2015

ballardw
Super User

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

data_null__
Jade | Level 19

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  
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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.

dcruik
Lapis Lazuli | Level 10

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!

ballardw
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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