how to get the previous value

[alepage]

Hello,

I would like to know how to get the previous value of var2 which is equal to var3 at each 6 observations. So if you look at exec number 3821 , the value of var2 is habi_statisti_20200106,and I want to get the previous value of var2 which is habi_statisti_20200103.

How do we do that ?

data want;
infile datalines delimiter=','; 
input var1 $4. var2 $25. var3 $25.;
datalines;
3791,habi_statisti_20200103,
3791,habi_statisti_20200103,
3791,habi_statisti_20200103,
3791,habi_statisti_20200103,
3791,habi_statisti_20200103,
3791,habi_statisti_20200103,habi_statisti_20191231
3801,habi_statisti_20200103,
3801,habi_statisti_20200103,
3801,habi_statisti_20200103,
3801,habi_statisti_20200103,
3801,habi_statisti_20200103,
3801,habi_statisti_20200103,habi_statisti_20191231
3811,habi_statisti_20200103,
3811,habi_statisti_20200103,
3811,habi_statisti_20200103,
3811,habi_statisti_20200103,
3811,habi_statisti_20200103,
3811,habi_statisti_20200103,habi_statisti_20191231
3821,habi_statisti_20200106,
3821,habi_statisti_20200106,
3821,habi_statisti_20200106,
3821,habi_statisti_20200106,
3821,habi_statisti_20200106,
3821,habi_statisti_20200106,habi_statisti_20200103
3831,habi_statisti_20200106,
3831,habi_statisti_20200106,
3831,habi_statisti_20200106,
3831,habi_statisti_20200106,
3831,habi_statisti_20200106,
3831,habi_statisti_20200106,habi_statisti_20200103
3841,habi_statisti_20200106,
3841,habi_statisti_20200106,
3841,habi_statisti_20200106,
3841,habi_statisti_20200106,
3841,habi_statisti_20200106,
3841,habi_statisti_20200106,habi_statisti_20200103
3851,habi_statisti_20200107,
3851,habi_statisti_20200107,
3851,habi_statisti_20200107,
3851,habi_statisti_20200107,
3851,habi_statisti_20200107,
3851,habi_statisti_20200107,habi_statisti_20200106
3861,habi_statisti_20200108,
3861,habi_statisti_20200108,
3861,habi_statisti_20200108,
3861,habi_statisti_20200108,
3861,habi_statisti_20200108,
3861,habi_statisti_20200108,habi_statisti_20200107
3871,habi_statisti_20200109,
3871,habi_statisti_20200109,
3871,habi_statisti_20200109,
3871,habi_statisti_20200109,
3871,habi_statisti_20200109,
3871,habi_statisti_20200109,habi_statisti_20200108
3881,habi_statisti_20200110,
3881,habi_statisti_20200110,
3881,habi_statisti_20200110,
3881,habi_statisti_20200110,
3881,habi_statisti_20200110,
3881,habi_statisti_20200110,habi_statisti_20200109
3891,habi_statisti_20200113,
3891,habi_statisti_20200113,
3891,habi_statisti_20200113,
3891,habi_statisti_20200113,
3891,habi_statisti_20200113,
3891,habi_statisti_20200113,habi_statisti_20200110
3901,habi_statisti_20200113,
3901,habi_statisti_20200113,
3901,habi_statisti_20200113,
3901,habi_statisti_20200113,
3901,habi_statisti_20200113,
3901,habi_statisti_20200113,habi_statisti_20200110
3911,habi_statisti_20200113,
3911,habi_statisti_20200113,
3911,habi_statisti_20200113,
3911,habi_statisti_20200113,
3911,habi_statisti_20200113,
3911,habi_statisti_20200113,habi_statisti_20200110
3921,habi_statisti_20200114,
3921,habi_statisti_20200114,
3921,habi_statisti_20200114,
3921,habi_statisti_20200114,
3921,habi_statisti_20200114,
3921,habi_statisti_20200114,habi_statisti_20200113
3931,habi_statisti_20200115,
3931,habi_statisti_20200115,
3931,habi_statisti_20200115,
3931,habi_statisti_20200115,
3931,habi_statisti_20200115,
3931,habi_statisti_20200115,habi_statisti_20200114
3941,habi_statisti_20200116,
3941,habi_statisti_20200116,
3941,habi_statisti_20200116,
3941,habi_statisti_20200116,
3941,habi_statisti_20200116,
3941,habi_statisti_20200116,habi_statisti_20200115
3951,habi_statisti_20200117,
3951,habi_statisti_20200117,
3951,habi_statisti_20200117,
3951,habi_statisti_20200117,
3951,habi_statisti_20200117,
3951,habi_statisti_20200117,habi_statisti_20200116
3961,habi_statisti_20200120,
3961,habi_statisti_20200120,
3961,habi_statisti_20200120,
3961,habi_statisti_20200120,
3961,habi_statisti_20200120,
3961,habi_statisti_20200120,habi_statisti_20200117
3971,habi_statisti_20200120,
3971,habi_statisti_20200120,
3971,habi_statisti_20200120,
3971,habi_statisti_20200120,
3971,habi_statisti_20200120,
3971,habi_statisti_20200120,habi_statisti_20200117
3981,habi_statisti_20200120,
3981,habi_statisti_20200120,
3981,habi_statisti_20200120,
3981,habi_statisti_20200120,
3981,habi_statisti_20200120,
3981,habi_statisti_20200120,habi_statisti_20200117
3991,habi_statisti_20200121,
3991,habi_statisti_20200121,
3991,habi_statisti_20200121,
3991,habi_statisti_20200121,
3991,habi_statisti_20200121,
3991,habi_statisti_20200121,habi_statisti_20200120
4001,habi_statisti_20200122,
4001,habi_statisti_20200122,
4001,habi_statisti_20200122,
4001,habi_statisti_20200122,
4001,habi_statisti_20200122,
4001,habi_statisti_20200122,habi_statisti_20200121
4011,habi_statisti_20200123,
4011,habi_statisti_20200123,
4011,habi_statisti_20200123,
4011,habi_statisti_20200123,
4011,habi_statisti_20200123,
4011,habi_statisti_20200123,habi_statisti_20200122
4021,habi_statisti_20200124,
4021,habi_statisti_20200124,
4021,habi_statisti_20200124,
4021,habi_statisti_20200124,
4021,habi_statisti_20200124,
4021,habi_statisti_20200124,habi_statisti_20200123
4031,habi_statisti_20200127,
4031,habi_statisti_20200127,
4031,habi_statisti_20200127,
4031,habi_statisti_20200127,
4031,habi_statisti_20200127,
4031,habi_statisti_20200127,habi_statisti_20200124
4041,habi_statisti_20200127,
4041,habi_statisti_20200127,
4041,habi_statisti_20200127,
4041,habi_statisti_20200127,
4041,habi_statisti_20200127,
4041,habi_statisti_20200127,habi_statisti_20200124
4051,habi_statisti_20200127,
4051,habi_statisti_20200127,
4051,habi_statisti_20200127,
4051,habi_statisti_20200127,
4051,habi_statisti_20200127,
4051,habi_statisti_20200127,habi_statisti_20200124
4061,habi_statisti_20200128,
4061,habi_statisti_20200128,
4061,habi_statisti_20200128,
4061,habi_statisti_20200128,
4061,habi_statisti_20200128,
4061,habi_statisti_20200128,habi_statisti_20200127
4071,habi_statisti_20200129,
4071,habi_statisti_20200129,
4071,habi_statisti_20200129,
4071,habi_statisti_20200129,
4071,habi_statisti_20200129,
4071,habi_statisti_20200129,habi_statisti_20200128
4081,habi_statisti_20200130,
4081,habi_statisti_20200130,
4081,habi_statisti_20200130,
4081,habi_statisti_20200130,
4081,habi_statisti_20200130,
4081,habi_statisti_20200130,habi_statisti_20200129
4091,habi_statisti_20200131,
4091,habi_statisti_20200131,
4091,habi_statisti_20200131,
4091,habi_statisti_20200131,
4091,habi_statisti_20200131,
4091,habi_statisti_20200131,habi_statisti_20200130
4101,habi_statisti_20200203,
4101,habi_statisti_20200203,
4101,habi_statisti_20200203,
4101,habi_statisti_20200203,
4101,habi_statisti_20200203,
4101,habi_statisti_20200203,habi_statisti_20200131
4111,habi_statisti_20200203,
4111,habi_statisti_20200203,
4111,habi_statisti_20200203,
4111,habi_statisti_20200203,
4111,habi_statisti_20200203,
4111,habi_statisti_20200203,habi_statisti_20200131
4121,habi_statisti_20200203,
4121,habi_statisti_20200203,
4121,habi_statisti_20200203,
4121,habi_statisti_20200203,
4121,habi_statisti_20200203,
4121,habi_statisti_20200203,habi_statisti_20200131
4131,habi_statisti_20200204,
4131,habi_statisti_20200204,
4131,habi_statisti_20200204,
4131,habi_statisti_20200204,
4131,habi_statisti_20200204,
4131,habi_statisti_20200204,habi_statisti_20200203
;
run;

[Tom]

Please explain why doing  this makes any sense?

Here is the solution:

Spoiler

Just use LAG() and then reset the value to missing except on every 6th observation.

data want;
  set have;
  var3=lag(var2);
  if mod(_n_,6) then var3=' ';
run;

[alepage]

Hello,

I have tested you code and we are close, except the lag does not seem to work.  When var2= habi_statisti_20200204, var3 should be equal to habi_statisti_20200203, when var2=habi_statisti_20200203 var3 should be equal to habi_statisti_20200131 and so on.

[Tom]

You cannot have a lagged value does not exist in the data.

Your actual rule must be something other than LAG.

How did you derive a result of  habi_statisti_20191231 from an existing value of habi_statisti_20200103 ?
What is the logic?

[alepage]

It is the last value of the previous year. We put it into a macro . and use it when var2 is equal to habi_statisti_20200103. I know that the lag function should work but up to now, it isn't

[Tom · Accepted solution]

You will need to have a second variable so you can RETAIN the value calculated when VAR2 changes.

This is the exceptional case where it makes sense to conditionally execute the LAG() function.

%let initial=habi_statisti_20191231;

data want;
  set have;
  by var2;
  if first.var2 then var3=coalescec(lag(var2),"&initial");
  retain var3;
  if mod(_n_,6)=0 then var3x=var3;
  same= var3x=want;
run;