Convert dates

[aska_ujita]

Hi there, I am having problems with my data.

I had two Excel sheet with the date format like this: MM/DD/YY.

I did the merge of two. And now half is as original format (MM/DD/YY) and othe half is all in5 digit numbers as "42373".

I am trying to make a class by Month by "DATEC=DAY(DATEC); MONTHC=MONTH(DATEC); YEARC=YEAR(DATEC);" but half is done succesfully and half not (all in ".").

Do I need to do something to make this procedure?

Thank you!

Best, Aska.

[ballardw]

HOW did you read the data into SAS from Excel?

One of the many headaches with using Excel for data interchange is the nonsense people do to files and you can run identical code (often Proc Import) and get different results for apparently similar files.

The numeric values you show look like the numeric offsets from 1 Jan 1900 (or sometimes actually 31 Dec 1899) that Excel uses for date management.

You may be able to recover the SAS date value with one of the y or z calculations below:

data example;
   x=42373;
   y= x + '01JAN1900'd;
   z= x + '31DEC1899'd;
   format y z date9.;
run;

Pick a known row from the sheet and check which date you want to use.

This sort of works because SAS uses 1Jan1960  as a date basis and dates prior to that are negative numbers of days.

Alternatively export to CSV from Excel and import those files. If the files are supposed to be the same then write a data step because the lengths of character variables can change when using proc import and combining the data sets may result in truncated data.

There generally is not any need to create groups "class" variables as you show, Formats assigned to the date values will work fine for many purposes. a "by month" could be generated using a MONYY or YYMM format. Formats are honored by most analysis, graphing and reporting procedures to create groups of like records.

[Kurt_Bremser]

With Excel, it's usually correct to use 30dec1899 as "zero" date, as Excel tries to use 01jan1900 as base date (day 1), but fumbles the ball with regards to 1900-02-29.

[ravig]

Please check the format of both the dates before merging , I assume One date is in DDMMYYY format and other should be char