turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How can I create an ordered row with lag variable?

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-07-2015 03:17 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gregor

05-07-2015 05:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gregor

05-07-2015 04:05 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to user24feb

05-07-2015 04:32 AM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gregor

05-07-2015 04:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gregor

05-07-2015 05:22 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-07-2015 05:30 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Gregor

05-07-2015 04:16 AM

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.