Help using Base SAS procedures

mark a missing if first in a by group

Reply
Super Contributor
Posts: 401

mark a missing if first in a by group

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

Super Contributor
Posts: 1,636

mark a missing if first in a by group

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

Super Contributor
Posts: 401

mark a missing if first in a by group

Thank you..

Super Contributor
Posts: 401

mark a missing if first in a by group

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

PROC Star
Posts: 7,492

mark a missing if first in a by group

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;

Super Contributor
Posts: 401

mark a missing if first in a by group

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..

PROC Star
Posts: 7,492

mark a missing if first in a by group

Just change the if first. statement to:

  if first.age

Super Contributor
Posts: 401

mark a missing if first in a by group

Thanks Art.. exactly what I need

Ask a Question
Discussion stats
  • 7 replies
  • 206 views
  • 3 likes
  • 3 in conversation