topic Re: Lag function with multiple by group variables in SAS Programming

Lag function with multiple by group variables

Saurabh_Rana — Mon, 07 Jun 2021 05:49:33 GMT

I want the following output by using the lag function using multiple by variables.

A B C Lag(C)

a d 1 .

a d 2 1

a e 3 .

b e 4 .

b e 5 4

c e 6 .

c f 7 .

c f 8 7

But the output I am getting is this-

A B C Lag(C)

a d 1 .

a d 2 1

a e 3 2

b e 4 3

b e 5 4

c e 6 5

c f 7 6

c f 8 7

Re: Lag function with multiple by group variables

andreas_lds — Mon, 07 Jun 2021 06:43:18 GMT

Please post the code you have, so that we can suggest improvements.

Something like

if first.b then Lag_C = .;

should help.

Re: Lag function with multiple by group variables

blueskyxyz — Mon, 07 Jun 2021 08:38:50 GMT

data have;
    length a $1 b$1;
    input a b c;
cards;
a d 1
a d 2
a e 3
b e 4
b e 5
c e 6
c f 7
c f 8
;
proc sort; by  a b c; 
run;

data want;
    set have;
    by a b c;
    c_=lag(c);
run;

Re: Lag function with multiple by group variables

andreas_lds — Mon, 07 Jun 2021 10:17:24 GMT

@blueskyxyz wrote:

data have;
    length a $1 b$1;
    input a b c;
cards;
a d 1
a d 2
a e 3
b e 4
b e 5
c e 6
c f 7
c f 8
;
proc sort; by  a b c; 
run;

data want;
    set have;
    by a b c;
    c_=lag(c);
run;

This will not create the required dataset, but the one @Saurabh_Rana is getting now. Also: why is variable C in the the by-statement in proc sort? This seems to be wrong.

Re: Lag function with multiple by group variables

Sajid01 — Mon, 07 Jun 2021 12:05:59 GMT

Hello @Saurabh_Rana

The code is doing the way it is written.
You want the Lag(C) values for selected rows,

You need to define a criteria and add a filter for it in your code.

Re: Lag function with multiple by group variables

Tom — Mon, 07 Jun 2021 12:13:20 GMT

Why are those other values supposed to be missing?

Is it because you want to treat A and B as grouping variables?

data want;
  set have;
  by a b ;
  lag_c=lag(c);
  if first.b then lag_c=.;
run;

Re: Lag function with multiple by group variables

ballardw — Mon, 07 Jun 2021 14:42:08 GMT

@Saurabh_Rana wrote:

I want the following output by using the lag function using multiple by variables.

A B C Lag(C)

a d 1 .

a d 2 1

a e 3 .

b e 4 .

b e 5 4

c e 6 .

c f 7 .

c f 8 7

But the output I am getting is this-

A B C Lag(C)

a d 1 .

a d 2 1

a e 3 2

b e 4 3

b e 5 4

c e 6 5

c f 7 6

c f 8 7

So, what is the INPUT?

Which are the BY variables?

What are the assignment rules?