Is it possible to make a backward loop? (that uses the previous variable in an array)

[FrejaB]

Hi, 

Is it possible to make a 'backward loop' and subtracting a value, ex. [i-4] in arrays and do loops?

In my data I have observations for a lot of different IDs and for each person I have a lot of different weeks and a lot of different events(also in weeks. If the person is at the event the dummy takes the value 1 and 0 for the rest of the event variables). All events occur in a given week in one year but I have observations for the years before and after the event for each person.

I want to make new variables where week0 is the week where the event occurs (ex event_201314=1) week1 the week after, week_2 is 2 wees before etc. I want to create both weeks before and after the event in my final data set. The new week variables should take the value of the original week_201301 but without referring to a specific date as the dates will be different for each person as the events occur over a year and each person only experience one event.

The data  looks something like this:

ID week_201301 week_201302   week_201303     .....week_201452   event_201302 

34             500                  200               200                       100                        1

23             0                      100               100                         0                          0

55             300                  100               100                         0                          0

..

And I want it to look like this:

ID week_201301 week_201302   week_201303     .....week_201452   event_201302    week_1    week0     week1

34             500             200                        200                       100                        1               500        200           200

23             0                 100                        100                       0                            0               ...            . ..              ...

55             300             100                        100                       0                            0               ...            ...               ...  

...

The week_ refers to weeks before the event and week to weeks after the event.

I have figured out how to make the weeks after the event, but not how to make the variables for weeks before the event. So essentially I have variables for a given week and I want to copy the value for the previous week into a new variable (depending on the first taking a value). My code looks like this:

 data my_data;
	set my_data;
	array event(*) event_201301-event_201352;
	array work(*) week_201201-week_201252 week_201301-week_201352 week_201401-week_201452;
	array week(*) week_50-week_1 week0-week50;
		do j=1 to 52;
                if event[j]=1 then week_2=work[j-2];
                if event[j]=1 then week_1=work[j-1];
                ....
                if event[j]=1 then week0=work[j];
                if event[j]=1 then week1=work[j+1]; 
                ....
                if event[j]=1 then week25=work[j+25];
end; run; 

   The forward loop that uses j+1 to j+50 works fine, but is it possible to do something similar by subtracting a value?

  if event[j]=1 then week25=work[j+25];

When I run this part of the code I get an error:

if event[j]=1 then week_2=work[j-2];
if event[j]=1 then week_1=work[j-1];

Is there some other way to do this than subtracting the values? Any help is much appreciated!

Thanks in advance.

[Reeza]

do i=50 to 1 by -1

Make sure to keep in mind if you do, i-1 you need to end one step earlier or start one step later in your looping otherwise if i=1 then i becomes 0, which doesn't exist => errors. 

[FrejaB]

Hi Reeza,

Thanks for your suggestion. Will this still allow the value to depend on the event if? The event only occurs in 2013 but the weeks_ can be in both 2013 and in 2012. Or would I need two different loops?

[Reeza]

It's kind of hard to say.

This may be easier if you can mock up a better example. Can you show what you have for 3 or 4 records and what you expect? Make sure the 3/4 records are different, ie some will cross years/weeks and some won't and some may not have any conditions met. 

If you post that we can probably help more with the code you need.

The way your array is structured I don't see any issues with crossing years unless you want WEEk assigned something else or a different set of variables.

I also second the thought that a long data set may be much easier to work with.

[FrejaB]

I am unfortunately not allowed to share the data, but maybe I can try to describe it a bit better:

I have an observation for each ID. For each ID I have a variable event_201301-event_201352 (so for each week in 2013, 01-52 are week numbers in the year). For each ID one of these dummy variables will take the value 1, the rest will take the value 0. So every ID experience the event only once. 

For each ID I have data for weeks (for example working hours.These can take a lot of different values) in the period from week_201201-week_201552. 

I have created variables week_26 to week78 for each ID that are empty. The variable week0 is conditioned on the event_2013..=1 and takes the value from the correspond week_2013.. variable. For example for ID 4 the event_201342=1 so the new week0 variable will take the value from the corresponding week_201342 variable. It works with the code provided when I am filling out the values after the event, but not with the weeks before the event. 

So essentially what I want is to make a time period that is 'different' for each person but will allow me to compare for each person for example what happens 25 weeks after the event, what happens 5 weeks before the event. For each person the new week variables can thus be for a different time period, but it should read in the variables corresponding to the event week. For every ID the event occurs in 2013 and the new week variable week_26-week78 will thus cross over years. 

I need to do something similar to condition on the event=1 and then taking the value from the previous week for each ID to take the value as week_1 (the week before week0, the event week). It works for the loop when I condition on the weeks after the week of the event, but not before:

...
do i=1 to 52;
if event_2013[j]=1 then week6=week_2013[j+6];

But not when I try to subtract the value from the loop:

...
do i=1 to 52;
if event_2013[j]=1 then week_6=week_2013[j-6];

I hope this is enough information, and thanks again for any suggestions. 

[Reeza]

I am unfortunately not allowed to share the data,

Then make fake data that's close to your real data but reflects your actual data structure. 

[PeterClemmensen]

Have you considered transposing your data from wide to long format? Usually it is not a good idea to have time structures in columns.