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
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
Thank you..
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
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;
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..
Just change the if first. statement to:
if first.age
Thanks Art.. exactly what I need
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.