How to retain value of last non zero variable

[naveen20jan]

Hi SAS Forum, I have the below dataset what I want is to retain the last non zero value for the particular name  . data have;   input name $ stockrate  ;   datalines; x 0.015 x 0.9 x 0 t .02 t 0 w .005 w 0 w 0 w 0 ; run; output  required x 0.015 x 0.9 x 0.9 t .02 t .02 w .005 w .005 w .005 w .005 Thanks in advance

[naveen20jan]

Posting again as question was not clear ..

Hi SAS Forum,

I have the below dataset what I want is to retain the last non zero value for the particular name  .

data have;

  input name $ stockrate   ;

  datalines;

x 0.015

x 0.9

x 0

t .02

t 0

w .005

w 0

w 0

w 0

;

run;

output   required

x 0.015

x 0.9

x 0.9

t .02

t .02

w .005

w .005

w .005

w .005

[Ksharp]

data have;
  input name $ stockrate   ;
  datalines;
x 0.015
x 0.9
x 0
t .02
t 0
w .005
w 0
w 0
w 0
;
run;
data want(drop=_stockrate);
 set have(rename=(stockrate=_stockrate));
 retain stockrate;
 if _stockrate ne 0 then stockrate=_stockrate;
run;

Xia Keshan

[naveen20jan]

Thanks Xai it worked perfectly ...

[naveen20jan]

But when i tried the same with  if satement and lag function the results were not as desired .

I used the below piece of code ,could you please help where i was wrong

data want;

retain  stockrate_new ;

set have;

stockrate_new = stockrate ;

if  stockrate eq 0 then stockrate_new = lag(stockrate);

drop stockrate ;

run;

[Ksharp]

OK. You could try this one :

data have;
  input name $ stockrate   ;
  datalines;
x 0.015
x 0.9
x 0
t .02
t 0
w .005
w 0
w 0
w 0
;
run;
data want(drop=stockrate);
 set have;
 retain  stockrate_new ;
 if stockrate ne 0 then stockrate_new=stockrate;
run;

Xia Keshan

[Tom]

That is because you have misused the LAG() function.  The LAG() function does not find the value from the previous observation in the data. Instead it maintains a stack so that it can find the value from the previous execution of that particular LAG() function call.  By executing it conditionally instead of on every observation the stack of values will be correct.

[naveen20jan]

Thanks Tom for such an explainatory answers ,but i am not able to understand your last line of the reply ie "By executing it conditionally instead of on every observation the stack of values will be correct." .

Could you please elaborate it a bit and also one more question from what i am able to understand .

Does that mean we cant use the lag() function with if statement conditionaly ?

[Josie1]

this is exactly what I'm trying to do. but I have more by variables. In addition to these, it may be useful to know that I have the two digit month and the 4 digit year as separate variables on this data set as well. this dataset spans from January 2012 - May 2018 and the first zero value for each id could start anywhere SO thats why i have the average inches for the year because some zeros are legitimate. If the yearly avg inches are zero then thats the to let me know that the bad data started either that year or sometime during the prior year. and i want to fill the inches for ID 101 for January 2018 with the value for January 2017 if it isn't 0 or if it is zero only use the zero if any of the rest of the inches2017 values for ID 101 are non zero after January. If all the inches2017 in 2017 after January 2017  are zero then apparently the bad data started and id prefer to use the january of the first year where where the average inches are non zero.

data have;

input ID date inches inches2016 inches2017 inches2018 avg_in2016 avg_in2017 avg_in2018 baddatastart;

datalines;

101 201601 75 75 . . 85 49 0 201707

101 201602 85 85 . .  85 49 0 201707

101 201603 95 95 . . 85 49 0 201707

101 201604 105 105 . . 85 49 0 201707

101 201701 55 . 55 . 85 49 0 201707

101 201702 58 . 58 . 85 49 0 201707

101 201703 42 . 42 . 85 49 0 201707

101 201704 43 . 43 . 85 49 0 201707

101 201801 0 . . 0 85 49 0 201707

101 201802 0 . . 0 85 49 0 201707

102 201601 777 777 . . 751 840 0 201704

102 201602 799 799 . . 751 840 0 201704

102 201603 729 729 . . 751 840 0 201704

102 201604 749 749 . . 751 840 0 201704

102 201701 888 . 888 . 751 840 0 201704

102 201702 828 . 828 . 751 840 0 201704

102 201703 724 . 828 . 751 840 0 201704

102 201704 0 . 0 . 751 840 0 201704

102 201801 0 . . 0 751 840 0 201704; run;