## Help with Excel type of vertical processing

Solved
Super Contributor
Posts: 506

# Help with Excel type of vertical processing

Hi Everyone,

I have a data with 4 variable.

The first 2 records have all information.

The rest must be filled based on the following rule:

*direction calculation;

if lag(direction)=1 and lag(var1)<low

OR

if lag(direction)=0 and lag(var1)>high

THEN  direction=1;
else direction=0;

*var1 calculation;
if direction=1 then var1=MIN(low, lag(high);
if direction=0 then var1=max(low, lag(high);

I am not sure how to do it.

Thank you so much.

HHC

data have;
input var1 direction low high;
datalines;
8     1    9 15
3    0    3 5
.     .    5 8
.     .    1 3
.     .    4 10
.     .    6 10
.     .    5 6
.     .    5 8
.     .    6 10

;run;

Accepted Solutions
Solution
‎10-25-2014 07:54 AM
Super User
Posts: 10,787

## Re: Help with Excel type of vertical processing

```data have;
input var1 direction low high;
datalines;
8     1    9 15
3    0    3 5
.     .    5 8
.     .    1 3
.     .    4 10
.     .    6 10
.     .    5 6
.     .    5 8
.     .    6 10
;run;
data want;
set have;
retain  lag_dir  lag_var1      lag_high;
if (lag_dir=1 and lag_var1 lt low ) or (lag_dir=0 and lag_var1 gt high ) then _direction=1;
else _direction=0;

if _n_ gt 2 then do;
direction=_direction;
if direction=1 then var1=MIN(low, lag_high);
if direction=0 then var1=max(low, lag_high);
end;
lag_dir=direction;
lag_var1=var1;
lag_high=high;
drop _: lag_:;
run;

```

Xia Keshan

All Replies
Super Contributor
Posts: 490

## Re: Help with Excel type of vertical processing

Could you provide the desired output.

Posts: 3,215

## Re: Help with Excel type of vertical processing

Be carefull with the lag function in SAS as it is NOT the loag function in Excel.

Try to understand the lag function of SAS not a reference to a previous record but as a queue you put something on and get back (FIFO).

Doing that you see the pitfall when executing  the lag conditionally or more often in a iteration.

---->-- ja karman --<-----
Solution
‎10-25-2014 07:54 AM
Super User
Posts: 10,787

## Re: Help with Excel type of vertical processing

```data have;
input var1 direction low high;
datalines;
8     1    9 15
3    0    3 5
.     .    5 8
.     .    1 3
.     .    4 10
.     .    6 10
.     .    5 6
.     .    5 8
.     .    6 10
;run;
data want;
set have;
retain  lag_dir  lag_var1      lag_high;
if (lag_dir=1 and lag_var1 lt low ) or (lag_dir=0 and lag_var1 gt high ) then _direction=1;
else _direction=0;

if _n_ gt 2 then do;
direction=_direction;
if direction=1 then var1=MIN(low, lag_high);
if direction=0 then var1=max(low, lag_high);
end;
lag_dir=direction;
lag_var1=var1;
lag_high=high;
drop _: lag_:;
run;

```

Xia Keshan

Super Contributor
Posts: 506

## Re: Help with Excel type of vertical processing

Thank you so much for your help, Xia.

HHC

Super Contributor
Posts: 506

## Re: Help with Excel type of vertical processing

By the way, can you tell me why the code when wrong when I change place like below.

The reason I did it is that, I want to see the new variable(lag_ declared before using it in the code.

I dont understand where SAS got the lag_: value to start the sequence.

HHC

data want;
set have;
drop _: lag_:;

retain  lag_dir  lag_var1      lag_high;
lag_dir=direction;
lag_var1=var1;
lag_high=high;

if (lag_dir=1 and lag_var1 < low ) or (lag_dir=0 and lag_var1 > high ) then _direction=1;
else _direction=0;

if _n_ gt 2 then do;
direction=_direction;
if direction=1 then var1=MIN(low, lag_high);
if direction=0 then var1=max(low, lag_high);
end;

run;

Super User
Posts: 10,787

## Re: Help with Excel type of vertical processing

NO. That is totally wrong code. if you want set lag_ variables , just remove . drop _: lag_:;

To clarify it more , the reason I put "lag_dir=direction;" at the bottom of code ,not beginning is just to make a LAG value as function LAG( ) .

when _n_=1  yours lag_dir would have the same value with direction, but mine is a missing value .

when _n_=2  yours lag_dir would have the same value with direction, but mine is lag(direction) .

Hope you understand it . the order of code is very important .

Xia Keshan

Super Contributor
Posts: 506

## Re: Help with Excel type of vertical processing

HHC

Super Contributor
Posts: 506

## Re: Help with Excel type of vertical processing

data have;
input var1 direction low high;
datalines;
8     1    9 15
3    0    3 5
.     .    5 8
.     .    1 3
.     .    4 100
.     .    6 10
.     .    5 6
.     .    5 8
.     .    6 10
;run;
data want;
set have;
*drop _: lag_:;

if (lag_dir=1 and lag_var1 < low ) or (lag_dir=0 and lag_var1 > high ) then _direction=1;
else _direction=0;

if _n_ gt 2 then do;
direction=_direction;
if direction=1 then var1=MIN(low, lag_high);
if direction=0 then var1=max(low, lag_high);
end;

retain  lag_dir lag_var1      lag_high; *this retain will help to keep value of this record to apply to next record in sequence;
lag_dir=direction;
lag_var1=var1;
lag_high=high;
run;

*the first record, no value of lag_, ==> _direction=0
*the second record, no value of lag_, ==> _direction=0

At the end of the second round, retain keep value of 2nd record to apply to next round
so lag_dir=direction=0, lag_var1=var1=3 lag_high=high=5.
In the 3rd round, now we have lag_ value to make comparison.

Note that: Since Direction and Var1 is updated row by row, we cannot use Lag() function and therefore has to go through that process.

For High value, we can use lag function directly. So Do not need retain lag_high But simply put Lag_high=Lag(high) in the first place as usual.

🔒 This topic is solved and locked.

Discussion stats
• 8 replies
• 363 views
• 3 likes
• 4 in conversation