Desktop productivity for business analysts and programmers

Recalculating with do-loop

Reply
Contributor
Posts: 34

Recalculating with do-loop

Hello,

 

I would like to re-calculate a time variable based on the previous values and I believe I will need to use a do-loop to re-calculate each individual observation so that the next observation is based on the conditional parameters of the previous observation after it has been re-calculated. 

 

Example:


pic.png

 

If I use a simple command such as:

 

if Minute_since <=3 then Re-Calculated_Time = (Lag)Time ;

else Re-Calculated_Time = Time;

 

The recalculated time doesn't take into account that the previous observation may have been re-calculated based on the condition, so the contemporary re-calculation is not based on the correct assumptions. 

 

Any help here? 

 

Garrett

Super User
Posts: 11,104

Re: Recalculating with do-loop

If I understand what you mean then I would compare the Lag(tim) to the Lag(re_calculated_time). If they differ then you have a condition where the previous record was actually recalculated.

You don't provide a rule for what to do so no example code. Also because of the way Lag works with If statements you may want to add temporary variables to hold the lag values and then drop:

 

Ltime = lag(time);

Lrecalc = lag(re_calculated_time);

<operations>

drop Ltime Lrecalc;

 

Note: you are using Re-calculated_time but that is not a valid SAS variable name, use _ instead of -.

Contributor
Posts: 34

Re: Recalculating with do-loop

Hi Ballardw, 

 

Thanks for your response. I know exaclty what I want to do but I don't know the name of the concept. 

 

Here is an example.... 

 

I have three times... 

1: 3:00

2: 3:02

3: 3:03

 

For the purpose of my analysis, these three times should be the same if they are less than or equal to 3 minutes apart from one another and they should be the time of the earliest observation. 

 

The time between 3:02 and 3:00 is 2 minutes.

The time between 3:03 and 3:02 is 1 minute.

The time between 3:03 and 3:00 is 3 minutes. 

 

If I recalculate with code such as:

 

if Time-lag(Time) <=3 then Time = lag(Time);

 

Then my recalculated times would be:

1. 3:00

2. 3:00

3. 3:02

 

This is because the condition in observation 3 is based on the original value of 3:02 and not the re-calculated value of 3:00. 

 

2) If 3:02-3:00 <=3 then Time = 3:00. CONDITION IS MET AND TIME IS RE-CALCULATED TO 3:00.

3) If 3:03-3:02 <=3 then Time = 3:02CONDITION IS MET AND TIME IS RE-CALCULATED TO 3:02.

 

But I want my times to be:

 

1. 3:00

2. 3:00

3. 3:00

 

Does that make sense? 

Super User
Posts: 11,104

Re: Recalculating with do-loop

I am very leary about actually removing original values from a data set. I hope you are either building a different data set or adding a variable.

One reason is suppose someone (your boss for instance) comes back and want to know what the impact of changing the critical interval from 3 minutes to 5 (or 2 or whatever). If you have replaced the original time value then you may not be able to perform the requested task.

Contributor
Posts: 34

Re: Recalculating with do-loop

Rest assured... all the original times are being kept.

 

I'm running vector autoregressions with literally thousands of exogenous variables that at times occur simulatenously or very close to each other in time....

 

For my purposes, if I have 10 exogenous effects all occuring within 3 minutes of each other they are all occuring at the same time. 

 

For example, if I have ten exogenous variables that occur at 3:00, then instead of 10 variables I have one variable....which is defined as 10 exogenous variables at 3:00. At this point of my analysis, I am not worried about the individual effect of all of those variables...just the combined effect. I worry about the individual effects later in my analysis. 

 

So I know how to combine the variables when tehy occur at the same time...but I need to know how to combine them when they are one, two, or three minutes away from each other. 

 

Garrett

Super User
Posts: 5,352

Re: Recalculating with do-loop

Assuming that the diagram shows the proper results that you would like ...

 

It would be easier to track the total minutes between, along these lines.

 

data want;

set have;

if _n_=1 then recalculated_time = time;

retain recalculated_time;

tot_minutes + minutes_between;

if tot_minutes > 3;

recalculated_time = recalculated_time + tot_minutes;

tot_minutes=0;

run;

 

If you wanted something different as the result, just add a column to your table to show what you are looking for.

Ask a Question
Discussion stats
  • 5 replies
  • 306 views
  • 0 likes
  • 3 in conversation