How can I create an ordered row with lag variable?

[Gregor]

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).

[user24feb]

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;

[Gregor]

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?

[Gregor]

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.

[RW9 · Accepted solution]

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.

[Gregor]

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

[RW9]

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.