I have a day of year column and need to convert it to a date.I want to find the exact date that the insurance coverage began for every person. I also have a year column
Have data:
id start_day year day_register 1 1 2008 365 1 20 2009 345 2 1 2008 365 2 1 2009 365 2 34 2010 331
Want data:
id start_day year reg_date 1 1 2008 01/01/2008 1 20 2009 20/01/2009 2 1 2008 01/01/2008 2 1 2009 01/01/2009 2 34 2010 03/02/2010
I searched up and find that the following command works in Oracle sql but does not work in SAS sql
select year, start_day, day_register, add_days( cast(cast(year*10000 + 100 as varchar(255)) as date), day_register + start_day - 2) from have;
I appreciate anyhelp
Advertisement
Answer
Looks like you just want to add START_DAY to the first day of the YEAR. You can use MDY() function to generate the first day of the year by using one for both the month and day numbers. Remember to subtract one day since the first day of the year is START_DAY number one and not number zero. Make sure to attach a date type format to it so it will print in a human readable way.
So if done in normal SAS code it might look like this:
reg_date = mdy(1,1,year)-1+start_day; format reg_date date9.;
The syntax in SQL is a little more confusing.
mdy(1,1,year)-1+start_day as reg_date format=date9.
You can use any other date type format you want. You could even use the DDMMYY10. format to have the components of the dates displayed in the potentially confusing day,month,year order used in your example listing.