topic How can I create an ordered row with lag variable? in SAS Procedures

How can I create an ordered row with lag variable?

Gregor — Thu, 07 May 2015 07:17:14 GMT

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

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

user24feb — Thu, 07 May 2015 08:05:20 GMT

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;

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

RW9 — Thu, 07 May 2015 08:16:45 GMT

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.

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

Gregor — Thu, 07 May 2015 08:32:07 GMT

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?

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

Gregor — Thu, 07 May 2015 08:47:30 GMT

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.

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

RW9 — Thu, 07 May 2015 09:22:58 GMT

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.

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

Gregor — Thu, 07 May 2015 09:30:15 GMT

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