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
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
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
Thanks Xai it worked perfectly ...
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;
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
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.
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 ?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: