BookmarkSubscribeRSS Feed
Anotherdream
Quartz | Level 8

Hello everyone. I recently ran into a 'feature' of SAS that I actually never noticed in a little over 2 years using the system.  Noting the error, I am actually not sure how to handle it going forward, so any help and ideas would be greatly appreciated!

Basically I am reading from a csv file one field, called "date1".  I am reading the field as an Informat Date1 MMDDYY10.  as I am expecting the date to come in a known date format of 03/18/2015.

However, one day the users sent me a file with the following in the date field. "41974".  What happened is the users got their data from Excel, and accidentally formatted the date field as "numeric", and 41,974 is the number of days between 1-1-1900 and "12-1-2014"

However when SAS read this number in, it actually produced the value "April 19 1974".  I was pretty shocked at this, but looking back I realize why it would do this..

However, what informat would I use to get around this problem?  I can't use MMDDYY10. anymore, since it incorrectly reads in numbers above as dates....

Any and all help would be appreciated!

7 REPLIES 7
Steelers_In_DC
Barite | Level 11

I'm not sure it's a problem with the informat.  It's a change in the way the file was formatted.  You'll have to translate excel date to sas date.

If you can put today() in for the date that would work nicely.  Depending on frequency and amount of data this might not be a good option but it could be a start.

data data2;

infile cards;

informat date1 mmddyy10.;

input date1;

cards;

04/19/1974

;

run;

data have2;

set data2;

number = intck('day',date1,'18mar2015'd);

run;

data want2;

format new_date mmddyy10.;

set have;

new_date = date1 + number;

run;

art297
Opal | Level 21

You could just use something like:

data have;

  input date1 $10.;

  format datewant date9.;

  if anypunct(date1) then datewant=input(date1,anydtdte10.);

  else datewant=input(date1,12.)-21916;

  cards;

41974

12-1-2014

03/18/2015

;

Anotherdream
Quartz | Level 8

DC I think you might have missed the point of my question. I don't want to have to know what the format of the string is coming in at the start of the function... I actually want this file to error out when the value is "41974", or to be smart enough to convert it to the correct date.

My question was more along the line of "is there a date informat that is smart enough to not try to read this number the way that sas is doing it."  There might not be.

Master

art297
Opal | Level 21

Since you have to trap errors how about something like:

data have;

  input date1 $10.;

  format datewant date9.;

  length error $20;

  if count(date1,'/') eq 2 then

   datewant=input(date1,?? mmddyy10.);

  if missing(datewant) then do;

    if anypunct(date1) then do;

      datewant=input(date1,anydtdte10.);

    error='unexpected format';

  end;

  else if not missing(input(date1,?? 12.)) then do;

    datewant=input(date1,12.)-21916;

    error='Excel number as date';

  end;

  else error='Invalid value';

  end;

  cards;

41974

heyman

12-1-2014

03/18/2015

;

Tom
Super User Tom
Super User

Probably easiest to read it as character and then add your own logic to validate and convert the value.  You can even trap the invalid values and generate you own error messages.

data _null_ ;

  input datestr $10.;

  select ;

     when (. ne input(datestr,??comma32.))  date=input(datestr,comma32.)+'01JAN1900'd - 2 ;

     when (. ne input(datestr,??mmddyy10.)) date=input(datestr,mmddyy10.);

     when (. ne input(datestr,??yymmdd10.)) date=input(datestr,yymmdd10.);

     when (. ne input(datestr,??ddmmyy10.)) date=input(datestr,ddmmyy10.);

     otherwise date=.;

  end;

  if date=. and datestr ne ' ' then put 'ERROR: Invalid Date String. ' datestr=;

  format date date9.;

  put (datestr date) (=);

cards;

1/1/1965

41,974

41974

Bad Date.

2014/01/30

30/12/2013

;;;;

Howles
Quartz | Level 8

Actually that difference is 41972

1    data _null_ ;

2    days_between = '01dec2014'd - '01jan1900'd ;

3    put days_between= ;

4    run ;

days_between=41972

art297
Opal | Level 21

: The subtraction to equate Excel date numbers with SAS date numbers.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 4867 views
  • 0 likes
  • 5 in conversation