Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Fixing Date with the first day of the month or year

Regular Contributor
Posts: 218

Fixing Date with the first day of the month or year

Hi All,

I have the following table in sas (start_date) that I imported from Excel. Data look weired because some data was entered as 05-FEB-2014, some as APR-2005 (without any day) and some as 2013 (without any day and month) in the excel spreadsheet that I imported the data from.

Can anyone show me how to fix them in sas according to:

05-FEB-2014 = stays the same

APR-2005 = 01-APR-2005 (first day of the month)

2013 = 01-JAN-2013 (first day of the year)

My output will look like this:

Start_Date           New Date

2011-00-00           01-JAN-2011

40073                  17-SEP--2009

2009-03-00           01-MAR-2009

37347                  01-APR-2002

1996-09-00           01-SEP-1996


Super Contributor
Posts: 337

Re: Fixing Date with the first day of the month or year

hey mlogan,

You can give it a try at writing a SAS macro that uses different formats/informats.

While you do that, a quick-and-dirty trick would be to fix that date column in Excel, and then import it to SAS. To make it the same in Excel, I would do as this steps (Format a date the way you want) and maybe copy the column and paste as values.

Good luck!


Super User
Super User
Posts: 7,942

Re: Fixing Date with the first day of the month or year

Excel and SAS calculate dates from a different base date.  You may just be able to take 23546 off the Excel date to get what you want:

data tmp;



  format a b date9.;


However as previous poster mentioned you would be far better off sorting the data out at source, and even better off by not using Excel - which is an unvalidated, unstructured, and basically unusable for any real purpose, bit of software.  If you want a data entry/storage, use a database, if you want a data transfer use an appropriate format - CSV, XML etc.

SAS Employee
Posts: 416

Re: Fixing Date with the first day of the month or year

Hello -

You may be able to take advantage of the MDY function (see:

Basically you will need to read you current string (start_date), decompose it into month, day, year information - and use MDY to create the SAS date value (and apply a format later on so you can see the date in your preferred format).

If month or day are missing then you can simply replace the values with 1 to create first day or month dates.

For unformatted values (for example 40073)  RW9's tip can be applied I think.

Hope I'm making sense.



Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation