Fixing Date with the first day of the month or year

[mlogan]

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

Thanks,

[M_Maldonado]

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!

-Miguel

[RW9]

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;

  a=40073;

  b=a-23546;

  format a b date9.;

run;

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.

[udo_sas]

Hello -

You may be able to take advantage of the MDY function (see: https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000199044.htm).

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.

Thanks,

Udo