Convert Chracter variable to data

[Malathi13]

Hi,
I have a question with dates. I have a character value for date of birth which is 36223 and I want to convert it to any date format either date9 or yymmdd or mmddyy. My excel shows that date as 3/5/1999 but when I imported the excel to SAS it shows as character with 36223, I tried to convert to sas date using date9 or any other formats it gives me 3/4/2059 which is very weird.

This is my program that I wrote to convert it to sas date format

data have;
set new;
birthdate=input(dob, yymmdd10.);
format birthdate date9.;
run;

I'm getting many missing values for other DOBs but some date of births that I get are not correct. As I mentioned above the character value of 36223 comes as 3/4/2059 after applying the format. Any help is greatly appreciated.

thanks
M

[Reeza]

SAS it shows as character with 36223

Can you show a proc contents of the data set before you run an INPUT() statement?

You may need to convert it to a number first and then apply a format. In the INPUT() statement, the format you provide is an informat, which should represent what the data currently looks like, which in this case is 8.

If you run something like this, what do you get?

numVar = input(dob, 8.);
format numVar date9.;

[Malathi13]

Hi,

When I ran 

numVar = input(dob, 8.);
format numVar date9.;

I got the numvar as 05MAR2059

M

[data_null__]

Looks like 36223 is a EXCEL date value. 

63   data _null_;
64      x = 36223+'30DEC1899'd;
65      y = '04MAR1999'd;
66      put (x y)(=);
67      put (x y)(=date9.);
68      run;

x=14307 y=14307
x=04MAR1999 y=04MAR1999

Updated based on comments from @Tom 

[Tom]

---

@data_null__ wrote:

Looks like 36223 is a EXCEL data value. 

45   data _null_;
46      x = 36223+'31DEC1899'd;
47      y = '05MAR1999'd;
48      /*3/5/1999*/
49      put (x y)(=);
50      put (x y)(=date9.);
51      run;

x=14308 y=14308
x=05MAR1999 y=05MAR1999

---

36,233 is March 4th in Excel.  For any date after Feb 1900 (raw value 61) need to use '30DEC1899'd.   If you actually have values of 59 and 60 you will need to come up with a plan for how to map them.

[Malathi13]

Hi,

I can use this data step because I have around 85,000 observations ( I mean date of Births). I can't do this for each observation or value.

data _null_;
64 x = 36223+'30DEC1899'd;
65      y = '04MAR1999'd;
66      put (x y)(=);
67      put (x y)(=date9.);
68      run;

36223 is just one date of birth, I have 85,000 date of births that are not 36223.

M

[Tom · Accepted solution]

What date do you think 36223 represents?  Do you think it is the value that Excel uses to represent 1999-03-04?

First convert '36223' into a number and then adjust the number to how SAS counts days.

birthdate=input(dob, 32.) + '30DEC1899'd ;
format birthdate date9.;

[Malathi13]

Hi Tom,

Thank you so much, that was so perfect!!!!!!!!!!

Thanks a lot!

M

[ballardw]

Or perhaps alternatively go back to the Excel file;  make sure the entire column is formatted the same for the date columns: do a File>Save As to CSV and import the CSV file.