Help using Base SAS procedures

How to Create A Lag Rolling "Product"?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to Create A Lag Rolling "Product"?

Hello,

I am trying to make a column that will be added to an existing data set. I need it to Multiply its number by the 6 previous entries (including its own for a total of 7) but only if the previous entries are under the same categories:

I have looked up Rolling lag "by group" but i have had no luck creating my result column.

Category ACategory BNumbers **Column I am looking to Add**
AB

2

2
AB

3

6
AB

3

18
AB472
AB

2

144
AB

2

288
AB2576
AB1288
BB

2

2
BB36
BA

2

2
BA48

All help is appreciated. Thank you.


Accepted Solutions
Solution
‎07-01-2014 11:41 AM
Super User
Posts: 5,085

Re: How to Create A Lag Rolling "Product"?

Sorry, I missed that part about the last 7 entries only.  Here's a modification:

data want;

set have;

by category_a category_b notsorted;

if numbers=. then factor=1;

else factor=numbers;

drop factor;

back7 = lag7(factor);

if first.category_b then do;

   new_column=factor;

   counter = 1;

end;

else do;

   new_column = new_column * factor;

   counter + 1;

   if counter > 7 then new_column = new_column / back7;

end;

retain new_column;

drop back7;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: How to Create A Lag Rolling "Product"?

Could use retain:

data have (drop=lsta lstb lstnum);
  attrib CatA catB format=$1. Numbers format=best.;
  infile cards;
  input cata $ catb $ numbers;
cards;
A B 2
A B 3
A B 3
A B 4
B B 2
B B 3
B A 2
;
run;

data want;
  set have;
  attrib result format=best.;
  retain lsta lstb lstnum;
  if _n_=1 or lsta ne cata or lstb ne catb then do;
    lsta=cata;
    lstb=catb;
    lstnum=numbers;
    result=numbers;
  end;
  else do;
    result=lstnum * numbers;
    lstnum=result;
  end;
run;

New Contributor
Posts: 4

Re: How to Create A Lag Rolling "Product"?

Thank you for your swift response. A few things,

1) It ended up creating new columns for result, CatA, CatB, 1st number and result, is there a way to avoid this and just have the result be the remaining column?

2) I forgot to mention this, but if there is nothing in the cells, then to treat the value in them as "1" ..... IE

A   B      3        3

A   B      -         3

A   B      2        6

Thanks!

Super User
Posts: 5,085

Re: How to Create A Lag Rolling "Product"?

You can simplify some of the logic using BY group processing:

data want;

set have;

by category_a category_b notsorted;

if numbers=. then factor=1;

else factor=numbers;

drop factor;

if first.category_b then new_column=factor;

else new_column = new_column * factor;

retain new_column;

run;

Good luck.

New Contributor
Posts: 4

Re: How to Create A Lag Rolling "Product"?

Can this be applied to also the last 7 Numbers? Yours worked in the way I wanted to except the new column kept compounding as opposed to keep a "rolling" product of only the last 7 entries.

Solution
‎07-01-2014 11:41 AM
Super User
Posts: 5,085

Re: How to Create A Lag Rolling "Product"?

Sorry, I missed that part about the last 7 entries only.  Here's a modification:

data want;

set have;

by category_a category_b notsorted;

if numbers=. then factor=1;

else factor=numbers;

drop factor;

back7 = lag7(factor);

if first.category_b then do;

   new_column=factor;

   counter = 1;

end;

else do;

   new_column = new_column * factor;

   counter + 1;

   if counter > 7 then new_column = new_column / back7;

end;

retain new_column;

drop back7;

run;

New Contributor
Posts: 4

Re: How to Create A Lag Rolling "Product"?

Thank you very much. Works Perfectly. Appreciate everyone else's efforts as well. Great community here.

Super User
Super User
Posts: 7,407

Re: How to Create A Lag Rolling "Product"?

Yep, just modify as:

data want (drop=lsta lstb lstnum);
  set have;
  attrib result format=best.;
  retain lsta lstb lstnum;
  if _n_=1 or lsta ne cata or lstb ne catb then do;
    lsta=cata;
    lstb=catb;
    lstnum=numbers;
    result=numbers;
  end;
  else do;
    if numbers=. then result=lstnum;

     else result=lstnum * numbers;
    lstnum=result;
  end;
run;

Super Contributor
Posts: 275

Re: How to Create A Lag Rolling "Product"?

data have;
input cata $ catb $ numbers;
cards;
A B 2
A B 3
A B .
A B 4
B B 2
B B 3
B A 2
;
run;

data want;
  set have;
  by cata;
  retain temp;
  if first.cata then add=numbers;
  else add=coalesce(numbers,1)*temp;
  temp=add;
  drop temp;
  run;

SAS Employee
Posts: 340

Re: How to Create A Lag Rolling "Product"?

The following code works even if you have more then 7 observations in one by group:

data have;

  attrib CatA catB format=$1. Numbers format=best.;

  infile cards;

  input cata $ catb $ numbers;

cards;

A B 2

A B 3

A B 3

A B 4

A B 2

A B 2

A B 2

A B 1

B B 2

B B 3

B A 2

B A 4

;

run;

data want;

  set have;

  keep catA catB result Numbers;  by catA catB notsorted;

  array lastNums[7] _temporary_;

  retain pointer;

  if first.catB then do;

  do i=1 to dim(lastNums);

  lastNums=1;

  end;

  pointer=1;

  end;

  lastNums[pointer]=coalesce(numbers,1);

  pointer=mod(pointer,dim(lastNums))+1;

  result=1;

  do i=1 to dim(lastNums);

  result=result*lastNums;

  end;

run;

Message was edited by: Gergely Bathó - modified the keep statement.

Contributor
Posts: 22

Re: How to Create A Lag Rolling "Product"?

Try this !!

data y1;

input cat_1 $ cat_2 $ numbers;

cards;

A B 2

A B 3

A B 3

A B 4

A B 2

A B 2

A B 2

A B 1

B B 2

B B 3

B A 2

B A 4

;

run;

proc sort data=y1;by cat_1 cat_2;run;

data y2;

set y1;

by cat_1 cat_2;

if first.cat_2=1 then do;

retain x 1;

x=numbers-1;

end;

x=(numbers)*x;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 390 views
  • 9 likes
  • 6 in conversation