Help using Base SAS procedures

How to retain value of last non zero variable

Reply
Contributor
Posts: 30

How to retain value of last non zero variable

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

Contributor
Posts: 30

Re: How to retain value of last non zero variable

Posted in reply to 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

Super User
Posts: 10,023

Re: How to retain value of last non zero variable

Posted in reply to naveen20jan
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

Contributor
Posts: 30

Re: How to retain value of last non zero variable

Thanks Xai it worked perfectly ...

Contributor
Posts: 30

Re: How to retain value of last non zero variable

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;

Super User
Posts: 10,023

Re: How to retain value of last non zero variable

Posted in reply to naveen20jan

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

Super User
Super User
Posts: 7,042

Re: How to retain value of last non zero variable

Posted in reply to naveen20jan

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.

Contributor
Posts: 30

Re: How to retain value of last non zero variable

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 ?

Ask a Question
Discussion stats
  • 7 replies
  • 745 views
  • 3 likes
  • 3 in conversation