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,
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
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.