Hi @yunfeizhao100 and welcome to the SAS Support Communities!
It is very difficult for SAS to read data from pictures. (Maybe this is why no solution has been provided yet.)
With sample data in the form of a DATA step ...
data have;
input cust_id period x1-x3;
cards;
1 1 1000 3 3
1 2 1000 5 2
1 3 1000 6 4
1 4 1000 7 6
1 5 1000 3 2
2 1 2000 6 5
2 2 2000 4 6
2 3 2000 3 1
;
... testing solutions is much easier. A simple solution could look like this:
data want;
set have;
by cust_id;
prev_x2=lag(x2);
if first.cust_id then y=x1;
else y=prev_x2*x3;
drop prev_x2;
run;
For each observation the x2 value of the previous observation is stored in variable prev_x2. Then, y=x1 for the first observation of each cust_id BY group, and y=prev_x2*x3 for the remaining observations.
Actually, the same results can be obtained with more concise code:
data want;
set have;
by cust_id;
y=ifn(first.cust_id, x1, lag(x2)*x3);
run;
But this (without further precautions) would cause an undesirable note in the log:
NOTE: Missing values were generated as a result of performing an operation on missing values.
...
(due to the missing value of lag(x2) in the very first observation).
Both solutions assume that your sample data is representative of your real data, i.e.
The first observation of each cust_id BY group represents the first period of the respective cust_id. (Note that I didn't use variable period.)
Similarly, the second, third, ... observation of each cust_id BY group represents the second, third, ... period of the respective cust_id (so that "previous observation" and "previous period" are equivalent for these observations).
If these assumptions are not met or if x2 or x3 can have missing values (and you want to avoid those notes in the log about missing values), we'll need to modify the suggested code.
... View more