topic Re: Iterative do loop to add days to a date in SAS Programming

Iterative do loop to add days to a date

sarahsasuser — Thu, 18 Apr 2019 20:07:09 GMT

Dear All,

I have data in wide format with a start date and an end date. I want to add 30 days to the start date until the end date. How do I do this?

The final data should look like this:

ID Start_date end_date start_time end_time

1 1/1/2019 5/1/2019 0 30

1 1/1/2019 5/1/2019 30 60

1 1/1/2019 5/1/2019 60 90

2 1/1/2018 9/1/2019 0 30

2 1/1/2018 9/1/2019 0 60

2 1/1/2018 9/1/2019 0 90

2 1/1/2018 9/1/2019 0 120

...

Thanks!

Re: Iterative do loop to add days to a date

Reeza — Thu, 18 Apr 2019 20:10:07 GMT

data want;
set have;

start_time = 0;end_time=0;

do date=start_date to end_date by 30;
end_time+30;
output;
start_time+30+1;
end;

run;

This may need changes depending on how your input data is specified, but will get you started. You do need to think about how to end the series when it's not exactly a 30 day time period.

@sarahsasuser wrote:

Dear All,

I have data in wide format with a start date and an end date. I want to add 30 days to the start date until the end date. How do I do this?

The final data should look like this:

ID Start_date end_date start_time end_time

1 1/1/2019 5/1/2019 0 30

1 1/1/2019 5/1/2019 30 60

1 1/1/2019 5/1/2019 60 90

2 1/1/2018 9/1/2019 0 30

2 1/1/2018 9/1/2019 0 60

2 1/1/2018 9/1/2019 0 90

2 1/1/2018 9/1/2019 0 120

...

Thanks!

Re: Iterative do loop to add days to a date

mcgaugh — Thu, 18 Apr 2019 20:16:06 GMT

It would be helpful if you could tell us what variables you are starting with. Just ID, start date, and end date or you starting with start_time and end_time also? Another question, are you wanting exactly 30 days from the start_date even if it is in a different month? For example, 30 days from 2/1/2019 would be in March not February.

Re: Iterative do loop to add days to a date

novinosrin — Thu, 18 Apr 2019 20:18:34 GMT

Can't agree more. I was about to mention the same but you got it squeaky clean. Plus @Reeza has also mentioned "You do need to think about how to end the series when it's not exactly a 30 day time period. "

Re: Iterative do loop to add days to a date

sarahsasuser — Thu, 18 Apr 2019 20:32:00 GMT

Thanks Reza, this is almost what I want. As other people have pointed out there is another variable in the dataset. This variable is called other date and the step after this is to see whether this date is within the time frame of start_time and end_time for each row. Reza's code creates multiple rows with the same value for other_date and then starts the counter over for each unique date, which it should not.

ID Start_date end_date start_time end_time other_date

1 1/1/2019 5/1/2019 0 30 1/30/2019

1 1/1/2019 5/1/2019 30 60 2/15/2019

1 1/1/2019 5/1/2019 60 90 3/10/2019

2 1/1/2018 9/1/2019 0 30 1/30/2018

2 1/1/2018 9/1/2019 0 60 2/30/2018

2 1/1/2018 9/1/2019 0 90 3/30/2018

2 1/1/2018 9/1/2019 0 120

Re: Iterative do loop to add days to a date

Reeza — Thu, 18 Apr 2019 20:36:49 GMT

I think you need to show more details of what you have and what you need then. If this all to determine if a date is within a period of enrollment, this may not be the best approach either.

Re: Iterative do loop to add days to a date

sarahsasuser — Thu, 18 Apr 2019 20:52:45 GMT

I need the start stop dates in 30 day increments like this for a survival analysis. The start, stop is my time scale and then the other_dates indicate whether the exposure occurred within that time period.