## How can I create an ordered row with lag variable?

Solved
Occasional Contributor
Posts: 19

# How can I create an ordered row with lag variable?

I would like to have the variable VAR in table1 modified in a way that

for each TIME = i CATEGORY VAR(i) le (VAR(i-1) holds.

I have made various attempts to achieve this, but there must be some lack of understanding or conceptional mistake in my thinking.

My most naive approach was:

data table2;

set table1;

if CATEGORY = lag(CATEGORY) then VAR = MIN(VAR, lag(VAR));

run;

But this obiously does not work.

Any ideas?

Thanks.

Gregor

PS. I would have liked to post my example as picture, sas file, excel or somehow, but the forum only excepts unfitting formats

(.bin, .dms, .lzh, .exe, .class, .so, .dll).

Accepted Solutions
Solution
‎05-07-2015 05:22 AM
Super User
Posts: 9,599

## Re: How can I create an ordered row with lag variable?

In which case for retain, retain another variable which keeps the maximum value so far:

data table2 (drop=lst_val);

set table1;

retain lst_val;

by category;

if first.category=1 then lst_val=var;

else do;

if var > lst_val then lst_val=var;

else var=lst_val;

end;

run;

So lst_val will increase within the group of category where var > lst_val.  If var is less than lst_val then it becomes lst_val.

All Replies
Super Contributor
Posts: 355

## Re: How can I create an ordered row with lag variable?

Couldn't test, but I think:

Data table2;

If _N_ ge 2 Then Set table1(Firstobs=1 Keep=Category Var Rename=(Var=Var_Lag Category=Category_Lag));

Set table1;

If Category eq Category_Lag Then Var=Min(Var,Var_Lag);

Run;

Occasional Contributor
Posts: 19

## Re: How can I create an ordered row with lag variable?

Thanks, but this does not work in my case when I had (within the same category)

VAR(5) = -8%

VAR(6) = -7%

VAR(7) = -5%

VAR(8) = -6%

VAR(9) = -3%

Then I get with your solution:

VAR(5) = -8%

VAR(6) = -8%

VAR(7) = -7%

VAR(8) = -6%

VAR(9) = -6%

But I want them all to be -8%

Could it be a problem that my categories are alpha-numeric?

Occasional Contributor
Posts: 19

## Re: How can I create an ordered row with lag variable?

This was an answer to user24feb. RW9s solution does not change any of the five numbers.

Maybe I did not explain myself well enough.

I shall copy my input table:

CATEGORY     TIME   VAR

A                          0         0%

A                          1          -1%

A                          2         -3%

A                          3          -5%

A                          4          -5%

A                          5          -5%

A                          6          -10%

A                          7          -10%

A                          8           -10%

A                          9          -2%

B                          0          0%

B                          1          0%

B                          2           -3%

B                         3         -5%

B                          4          -6%

B                         5          -8%

B                         6             -7%

B                         7            -5%

B                         8              -6%

B                          9             -3%

After the data step, I want all category B from TIME 5 on to be -8%, A TIME 9 to be -10%

Maybe I can program it with a macro loop, but I thought there might be a more elegant way.

Solution
‎05-07-2015 05:22 AM
Super User
Posts: 9,599

## Re: How can I create an ordered row with lag variable?

In which case for retain, retain another variable which keeps the maximum value so far:

data table2 (drop=lst_val);

set table1;

retain lst_val;

by category;

if first.category=1 then lst_val=var;

else do;

if var > lst_val then lst_val=var;

else var=lst_val;

end;

run;

So lst_val will increase within the group of category where var > lst_val.  If var is less than lst_val then it becomes lst_val.

Occasional Contributor
Posts: 19

## Re: How can I create an ordered row with lag variable?

Works if I change the > to <.   Thanks a lot.

Super User
Posts: 9,599

## Re: How can I create an ordered row with lag variable?

Assuming your data is right, then you could use:

data table2;

set table1;

retain lst_cat lst_val;

if _n_=1 then do;

lst_cat=category;

lst_val=.;

end;

else do;

if CATEGORY = lst_cat then VAR = MIN(VAR, lst_var);

lst_cat=category;

lst_var=var;

end;

run;

Not tested as no test/output given.  Note that its easier for us if you post test data in a datastep in the post, and give example output as text in the post.

🔒 This topic is solved and locked.

Discussion stats
• 6 replies
• 279 views
• 0 likes
• 3 in conversation