topic Re: Fixing Date with the first day of the month or year in SAS Data Science

Fixing Date with the first day of the month or year

mlogan — Mon, 17 Aug 2015 18:22:45 GMT

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,

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

M_Maldonado — Tue, 18 Aug 2015 11:50:13 GMT

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

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

RW9 — Tue, 18 Aug 2015 12:43:24 GMT

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.

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

udo_sas — Tue, 18 Aug 2015 17:05:43 GMT

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