topic mark a missing if first in a by group in SAS Procedures

mark a missing if first in a by group

podarum — Mon, 19 Mar 2012 19:41:23 GMT

Hi, I'm trying to mark a few variables as missing when the observation in a BY group is first.

I have tried this, but not really worked..

data want;

set have;

by Store Product;

if first.Store and first.Product then Var1 = . and Var2 = . and Var3 = . ;

run;

HAVE

Store Product Var1 Var2 Var3

A A1 2 4 6

A A1 3 5 7

A A1 2 5 4

B A1 1 3 3

B A1 4 5 5

B A1 5 3 2

B B1 2 4 3

B B1 4 7 4

C C2 4 3 3

C C2 6 7 6

WANT

Store Product Var1 Var2 Var3

A A1 . . .

A A1 3 5 7

A A1 2 5 4

B A1 . . .

B A1 4 5 5

B A1 5 3 2

B B1 . . .

B B1 4 7 4

C C2 . . .

C C2 6 7 6

mark a missing if first in a by group

Linlin — Mon, 19 Mar 2012 19:50:11 GMT

data want;

set have;

by store product;

if first.product then do;

var1=.;var2=.;var3=.;

end;

proc print;run;

Obs Store Product Var1 Var2 Var3

1 A A1 . . .

2 A A1 3 5 7

3 A A1 2 5 4

4 B A1 . . .

5 B A1 4 5 5

6 B A1 5 3 2

7 B B1 . . .

8 B B1 4 7 4

9 C C2 . . .

10 C C2 6 7 6

mark a missing if first in a by group

podarum — Mon, 19 Mar 2012 20:03:02 GMT

Thank you..

mark a missing if first in a by group

podarum — Mon, 19 Mar 2012 20:33:49 GMT

Any idea why this is not working ?...

I'm trying to get a lag from the previous Var1, through the BY group.. but not getting it.. here's what I mean..

By Store Product;

Lag_Var1 = Lag(Var1);

Lag_Var1_2 = Lag(Lag(Var1));

Lag_Var1_3 = Lag(Lag(Lag(Var1)));

Shouldn't the BY grouping only look at each store and Product separately..??

Instead I'm getting overlapping, for example the Store = B and Product A1, does a Lag_Var1_2 based on the Store=A and Product =A1

mark a missing if first in a by group

art297 — Mon, 19 Mar 2012 21:32:43 GMT

Is the following what you are trying to do?

proc sort data=sashelp.class (rename=(weight=var1))

out=want;

by sex;

run;

data want (drop=counter);

set want;

by sex;

Lag_Var1 = Lag(Var1);

Lag_Var1_2 = Lag2(Var1);

Lag_Var1_3 = Lag3(Var1);

if first.sex then counter=1;

else counter+1;

if counter le 3 then call missing(Lag_Var1_3);

if counter le 2 then call missing(Lag_Var1_2);

if counter eq 1 then call missing(Lag_Var1);

run;

mark a missing if first in a by group

podarum — Tue, 20 Mar 2012 13:33:53 GMT

Hi Art, your code does what I want, but if for example I have another By group vraiable let's say the AGE:

proc sort data=sashelp.class (rename=(weight=var1))

out=want;

by sex age;

run;

data want (drop=counter);

set want;

by sex age;

Lag_Var1 = Lag(Var1);

Lag_Var1_2 = Lag2(Var1);

Lag_Var1_3 = Lag3(Var1);

if first.sex then counter=1;

else counter+1;

if counter le 3 then call missing(Lag_Var1_3);

if counter le 2 then call missing(Lag_Var1_2);

if counter eq 1 then call missing(Lag_Var1);

run;

Does not give me what I need.

Want it to start fresh at each Sex and Age... similar to how you have it at only Age..

mark a missing if first in a by group

art297 — Tue, 20 Mar 2012 13:35:38 GMT

Just change the if first. statement to:

if first.age

mark a missing if first in a by group

podarum — Tue, 20 Mar 2012 13:57:50 GMT

Thanks Art.. exactly what I need