Import date, not 1920 or 1960

[jbrau123]

Hello

I am trying to import data with a date variable. It has number format, which, i guess, wouldn't be a problem if the variable used SAS date format, but when I use the date9. format the dates are wrong. I am getting dates that are simply much later than they are supposed to be. For example "25933" should be december 31st 1970, but I get a much later date (I know this because I have the original data with both variables). I assume it has something to do with what day is zero and that this variable does not have jan 1st 1960 as zero. So how do I get the right date format?

data example;
   input ID Birthday ; 
   datalines;          
1 25933

2 25771

3 27095

4 21778
;

[ballardw]

You says you are "importing" data. What file format? CSV, text, Excel or something else? How are you importing? Using Proc Import statements, data step a wizard or something else? You code would be best and if practical an example of your source file.

What do the dates look like in the other file?

We have to ask a number of questions because there are people using SAS on this forum that keep thinking a value like 1810 is a "date" when they have a two digit year and 2 digit month. Others think datetime values are dates (not in SAS, different units). Some other source files

[jbrau123]

Hello

Thank you for your reply

In Excel I have it as both numbers and DDMMYYYY so the observation "25933" is 31DEC1970.

Kind regards,

J

[jbrau123]

(and yes, I am using proc import)

[mkeintz]

Your import procedure is not recognizing the excel column as holding excel date values, so it does not make the appropriate data value conversion.  Excel has internal numeric value 1 as jan 1, 1900 (internal numeric value of 25933 for dec 31, 1970).  SAS has internal value 1 as Jan 2, 1960, so the date9 format for 25933 is 01jan2031.

The fix, in theory, would be to get the sas numeric value of Jan 2, 1960 minus the sas numeric value for Jan 1, 1900, and subtract that from your numeric excel values.  The resulting sas value could then be assigned a display format of date9. to get what you want.

Here is the slightly wrong result, based on that theory:

08  data _null_;
109    x=25933;
110
111    ex1='01jan1900'd;
112    sas1='02jan1960'd;
113
114    want=x-('02jan1960'd-'01jan1900'd);
115    put want=date9.;
116  run;

want=01JAN1971
NOTE: DATA s

It's slightly wrong because Excel erroneously assumes that 1900 was a leap year.  So, excel assumes 1 more day between 01jan1900 and 02jan196 than there actually was.  To compensate for that subtract 1 more unit from your starting value:

117  data _null_;
118    x=25933;
119
120    ex1='01jan1900'd;
121    sas1='02jan1960'd;
122
123    want=x-('02jan1960'd-'01jan1900'd)-1;
124    put want=date9.;
125  run;

want=31DEC1970

BTW, you might wholly blame the designers of excel for this problem, but they only share the blame.  They knowingly replicated this error in the earlier, popular, spreadsheet software Lotus-123.

[Kurt_Bremser]

---

@mkeintz wrote:

They knowingly replicated this error in the earlier, popular, spreadsheet software Lotus-123.

---

... and still refuse to fix it the way it's fixed in (all) other major office software suites.

[mkeintz]

@Kurt_Bremser:

I mostly agree but ...

... the problem has not been entirely neglected by Microsoft.  It exists for the "1900 date system", but Microsoft let's you specify an alternative "1904 date system", which doesn't suffer from the problem because the earliest supported date is Jan 1, 1904.  The 1904 system is default for Mac versions, but I believe the 1900 system is still default for Windows.  You'd have to go through a well-documented process to convert to 1904 system.

Given that @jbrau123 described 25933 as 31dec1970, we know that the 1900 system is being used.  If it was the 1904 system, then one would use '02jan1960'd-'01jan1904'd as the amount to subtract - without the extra "-1".

[jbrau123]

Hello mkeintz

Thank you for your reply

I am trying to follow your advice but unfortunately I can't make it work. I'm a rookie so I am trying to understand step by step what you want me to do, but when I run this script, nothing happends.

"x=":               defines a constant?

_null_:         based on SAS documentation this gives me no output, so I guess I don't quite understand why this is used

d after '...':   simply formats as a date, right?

and then you take x and subtract the diff of ex1_ variable from sas1 and give it the date9. format

So it looks like you want me to define a variable 'want'  as x minus the diff between those two dates, but I don't get this variable in my dataset and 25933 was just an example, it is not a constant. Shouldn't Birthday be mentioned somewhere?

I'm assuming that I misunderstand something...

[Kurt_Bremser]

---

@jbrau123 wrote:

Hello mkeintz

Thank you for your reply

I am trying to follow your advice but unfortunately I can't make it work. I'm a rookie so I am trying to understand step by step what you want me to do, but when I run this script, nothing happends.

"x=":               defines a constant?

_null_:         based on SAS documentation this gives me no output, so I guess I don't quite understand why this is used

d after '...':   simply formats as a date, right?

and then you take x and subtract the diff of ex1_ variable from sas1 and give it the date9. format

So it looks like you want me to define a variable 'want'  as x minus the diff between those two dates, but I don't get this variable in my dataset and 25933 was just an example, it is not a constant. Shouldn't Birthday be mentioned somewhere?

I'm assuming that I misunderstand something...

---

There are no such things as named constants in the SAS language. x is just a variable set to a fixed value, for purposes of having an example. One could input x, and use a cards; section to supply a series of values instead.

A data _null_ step is a step in a SAS program that does not create a dataset, but allows one to use the full power of the data step language. data _null_ steps are often used to write data from datasets to external files, or create macro variables when more complicated calculations are needed (that would unnecessarily blow up a %let macro statement).

A string in the format DDMMMYYYY (month written with three characters) and immediately followed by a d is (as you rightfully observed) a "date literal". There are also time (t), datetime (dt), hexadecimal (x), and so-called name literals (n) which allow the use of non-standard names (the latter not recommended for serious use, though).

The whole step by @mkeintz is just an example for one single value, to be adapted to your variables and needs.

[dandelionjmy]

This helped me. Thank you!

[ballardw]

---

@jbrau123 wrote:

Hello

Thank you for your reply

In Excel I have it as both numbers and DDMMYYYY so the observation "25933" is 31DEC1970.

Kind regards,

J

---

Likely the easiest solution is first, make sure all your date columns in Excel have the same format applied to all the values in the column.

Then save the file as CSV.

Import that file with the Proc Import option Guessingrows=max;

By default only a few rows of data are exposed to the Import engine for guessing which can lead to unexpected data types if the first rows do not provide examples of all of the values.

Ensuring all of the data values have the same format is important, especially if you have had multiple people working on that spreadsheet. I have received such where some of the dates were Excel date numeric values with date formats and some were character values. I can tell you from experience Proc Import does not handle that well. The CSV approach helps. A lot.

At least they have fixed the bug in Excel that allowed entering 1/0/1900 as a valid date as of Office 365 / 2016.

[Kurt_Bremser]

Subtract

'01jan1960'd - '30dec1899'd

from the value and see if you get the desired date.

[PaulAThompson]

SAS does date as the number of days after Jan 1 1960. You probably imported this from Excel which does date as the number of days after Jan 1 1900. You merely need to subtract the difference between the correct date and the value you have. To determine this, use the MDY function to determine the actual number for your date. If your date is Sept 10 1972 enter 

data _null_;

        dval=mdy(9,10,1972);

        put dval=;

run;

Then subtract that value from the value in the variable. That gives you the difference. Subtract that difference from all dates, and you should be all set.

[jbrau123 · Accepted solution]

Thank you all for replies, I fixed it by formatting the date in Excel