-1

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

JNevill
  • 46,980
  • 4
  • 38
  • 63
kiabar
  • 63
  • 6

1 Answers1

1

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.

Tom
  • 47,574
  • 2
  • 16
  • 29