how to convert character date to numeric format

[thanikondharish]

data ex ;
date='22/11/2016';
output;
date='41997';
output;
run;

we have date values(in character format) one is mmyydd and another is days format. How to convert to numeric format with yymmdd10.(numeric foramat)

[andreas_lds]

Use the input function or assign a proper informat while importing the data from file.

proper_date = input(str_date, ddmmyy10.);

[Kurt_Bremser]

The value of 41997 points directly to a failed import from Excel. Make sure that the Excel file has a consistent date format throughout the column. If there are special values (like "NA" for a missing date), save the sheet to a text file (csv, tab-separated, whatever floats your boat) and read the data from there, using a custom informat.

To convert the raw Excel number to a SAS date, do this:

num_date = input(date,5.) + "30dec1899"d;

Make it conditional upon the length of the string.

[ballardw]

With the value of that second "date" I would say go back to Excel and make sure the entire column either numeric or character for the date. The reread or whatever you did to get the data set into SAS. Then ask again.

Do  you know which date is supposed to be the result from 41997? I ask because depending on the very likely source of that value as an Excel date the version of the software will want one of two offset dates? Large economy sized hint: There a many different coding systems for date values and conversion between different ones means that you really should provide a known date for some random number like 41997. Second hint: try not to pick a date in a leap year for the example.

Second, your other date is not in mmyydd (???) format. Month has a range of 1 to 12, exactly. So 22 cannot be a month. That would have to be ddmmyy.

Here's where I would start:

data want;
   set ex;
   if length (date)=10 then newdate= input(date,ddmmyy10.);
   else if length (date)=5 then newdate = input(date,5.)+'31Dec1899'd;
   /* depending on the source version of excel that date offset may be '01JAN1900'd or '30DEC1899'd*/
   format newdate yymmdd10.;
run;

The '31DEC1899'd is the equivalent of one of the Excel numeric date value of 0, which may appear as 00JAN1900 (!!).

If the new date for that value is supposed to be 25DEC2014 then you're good to go. If it supposed to be another date add/subtract date from '31DEC1899'd to get the desired result.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.