topic Re: Converting date to number forth and back in SAS Programming

Converting date to number forth and back

Phil_NZ — Mon, 01 Feb 2021 08:40:13 GMT

Hi SAS Users,

When I import data, I forget to format the data for the value BDATE, the raw data is as below:

Type         BDATE
134495     17/10/1994
134625     22/12/1993
13811D     25/06/2002
149482     2/01/1995

And because of importing from excel file to sas7bdat, the data now is

Type           BDATE
134495         34624
134625         34325
13811D         37432
149482         34701

Now I want to calculate

firm_age= log(1+(2020 - year (BDATE))); from the dataset now.

Could you please help me to sort it out?

Many thanks and warm regards.

Re: Converting date to number forth and back

Kurt_Bremser — Mon, 01 Feb 2021 08:54:26 GMT

Add

'30dec1899'd

(called a "date literal", which is a special form of a numeric constant) to these values, and apply a date format.

Background: SAS counts dates as days, with 1960-01-01 as day zero, while Excel starts with 1900-01-01 as day 1. Since Excel also (wrongfully) considers 1900 a leapyear, we have to go back an additional day.

Re: Converting date to number forth and back

Phil_NZ — Mon, 01 Feb 2021 11:20:18 GMT

Hi @Kurt_Bremser !

Thank you for your explanation, could you please adding the code to extract the year or else, I do not know how to create the code to convert from number to date and retrieve the year from it. And I do not know how to add

'30dec1899'd

to the code as well.

Many thanks and warmest regards.

Re: Converting date to number forth and back

Kurt_Bremser — Mon, 01 Feb 2021 12:54:52 GMT

var = var + '30dec1899'd;
format var yymmdd10.;

Just a simple addition, and assignment of a date format.

Re: Converting date to number forth and back

Tom — Mon, 01 Feb 2021 13:47:19 GMT

Typically this happens when the column in excel has mixed numeric (SAS and Excel store dates as numbers) and character values. So SAS defines the variable as character and the date values get stored as digit strings that represent the number that Excel uses for the date.

So you probably need to fist convert BDATE into a number and then add (the negative number) '30DEC1899'D to convert the number to a SAS date.

SASDATE=input(BDATE,32.)+'30DEC1899'd ;

Example using the strings from your posted listing.

815   data _null_;
816     do BDATE = '34624','34325','37432','34701' ;
817       SASDATE=input(BDATE,32.)+'30DEC1899'd ;
818       put bdate= $quote. sasdate= yymmdd10. ;
819     end;
820   run;

BDATE="34624" SASDATE=1994-10-17
BDATE="34325" SASDATE=1993-12-22
BDATE="37432" SASDATE=2002-06-25
BDATE="34701" SASDATE=1995-01-02

Once you have an actual date value your expression should work.

firm_age= log(1+(2020 - year (SASDATE)));