08-17-2015 02:22 PM
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
08-18-2015 07:50 AM
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.
08-18-2015 08:43 AM
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:
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.
08-18-2015 01:05 PM
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.