DATA Step, Macro, Functions and more

Resetting Lag Fields

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Resetting Lag Fields

Is there a way to reset Lag fields to 0?  I have the following code and want to reset the values in x1-11 when I move to a new by group based on level 1 and level 2. I tried resetting in a do loop but that is not working.....thanks for the help!

 

DATA ADD_LAGS;
 SET WORK.SUMMARY_RET_DATA;
BY level1  level2  YYYYMM SUM_OF_BILLINGS;
*retain x1-x11 Rolling_12;
if first.level1 then do;
                              x1=0;
                              x2=0;
                              x3=0;
                              x4=0;
                              x5=0;
                              x6=0;
                              x7=0;
                              x8=0;
                              x9=0;
                              x10=0;
                              x11=0;
                           end;

x1 =lag1(sum_of_Billings);
x2 =lag2(sum_of_Billings);
x3 =lag3(sum_of_Billings);
x4 =lag4(sum_of_Billings);
x5 =lag5(sum_of_Billings);
x6 =lag6(sum_of_Billings);
x7 =lag7(sum_of_Billings);
x8 =lag8(sum_of_Billings);
x9 =lag9(sum_of_Billings);
x10=lag10(sum_of_Billings);
x11=lag11(sum_of_Billings);

If _N_ = 1 then Rolling_12 = sum_of_Billings;
    Else if _N_ < 12 then Rolling_12 + sum_of_Billings;
    Else Rolling_12 = sum(sum_of_Billings,x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11);

                
run;


Accepted Solutions
Solution
‎07-12-2017 11:05 AM
Super User
Super User
Posts: 7,074

Re: Resetting Lag Fields

You could first calculate the lagged values then reset the ones that don't apply.

DATA ADD_LAGS;
  SET WORK.SUMMARY_RET_DATA;
  BY level1  level2  YYYYMM SUM_OF_BILLINGS;

x1 =lag1(sum_of_Billings);
x2 =lag2(sum_of_Billings);
x3 =lag3(sum_of_Billings);
x4 =lag4(sum_of_Billings);
x5 =lag5(sum_of_Billings);
x6 =lag6(sum_of_Billings);
x7 =lag7(sum_of_Billings);
x8 =lag8(sum_of_Billings);
x9 =lag9(sum_of_Billings);
x10=lag10(sum_of_Billings);
x11=lag11(sum_of_Billings);
    array x x1-x11 ;
    n+1;
    if first.level1 then n=1;
    do i=n to dim(x) ;
       x(i)=0;
    end;
   ...

View solution in original post


All Replies
Solution
‎07-12-2017 11:05 AM
Super User
Super User
Posts: 7,074

Re: Resetting Lag Fields

You could first calculate the lagged values then reset the ones that don't apply.

DATA ADD_LAGS;
  SET WORK.SUMMARY_RET_DATA;
  BY level1  level2  YYYYMM SUM_OF_BILLINGS;

x1 =lag1(sum_of_Billings);
x2 =lag2(sum_of_Billings);
x3 =lag3(sum_of_Billings);
x4 =lag4(sum_of_Billings);
x5 =lag5(sum_of_Billings);
x6 =lag6(sum_of_Billings);
x7 =lag7(sum_of_Billings);
x8 =lag8(sum_of_Billings);
x9 =lag9(sum_of_Billings);
x10=lag10(sum_of_Billings);
x11=lag11(sum_of_Billings);
    array x x1-x11 ;
    n+1;
    if first.level1 then n=1;
    do i=n to dim(x) ;
       x(i)=0;
    end;
   ...
Super User
Posts: 7,832

Re: Resetting Lag Fields

Follow @Tom's suggestion.

 

Don't post example data as Excel files. Excel spreadsheets do not convey important information about the dataset structure (variable types, lengths, formats), and are blocked at many corporate sites for security reasons.

Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your dataset to a datastep that can be posted in a code window (buttons ##6 and #7 on top of the posting window) and allows recreation of your dataset by a simple copy/paste and run.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 105 views
  • 1 like
  • 3 in conversation