topic Re: Repeat zero value with lagged value in SAS Procedures

Repeat zero value with lagged value

deleted_user — Mon, 29 Mar 2010 11:32:15 GMT

Hi All,

I have the data as following: lastsell with some zero values. I just want to replace all zero value with last available data. Eg. From 0 to 40 and 0 to 37.5

please note that i have already input the data into sas file.

security_symbol dtstart dtend lastsell
AA 03JAN07:10:00:00 03JAN07:11:00:00 40
AA 03JAN07:11:00:00 03JAN07:12:00:00 0
AA 03JAN07:14:30:00 03JAN07:15:30:00 0
AA 03JAN07:15:30:00 03JAN07:16:30:00 40
AA 04JAN07:10:00:00 04JAN07:11:00:00 37.5
AA 04JAN07:11:00:00 04JAN07:12:00:00 0
AA 04JAN07:14:30:00 04JAN07:15:30:00 0
AA 04JAN07:15:30:00 04JAN07:16:30:00 0

I want to use retain and lag function to do the coding, but just cannot make it.

Please give me a favor to suggest me the coding. Your help is always appreciated. Thank you very much.

Regards,
Chaoji

Re: Repeat zero value with lagged value

Patrick — Mon, 29 Mar 2010 12:41:56 GMT

data have;
infile datalines truncover dlm=' ';
input security_symbol $ dtstart anydtdtm17. dtend anydtdtm17. lastsell ;
format dtstart dtend datetime21.;
datalines;
AA 03JAN07:10:00:00 03JAN07:11:00:00 40
AA 03JAN07:11:00:00 03JAN07:12:00:00 0
AA 03JAN07:14:30:00 03JAN07:15:30:00 0
AA 03JAN07:15:30:00 03JAN07:16:30:00 40
AA 04JAN07:10:00:00 04JAN07:11:00:00 37.5
AA 04JAN07:11:00:00 04JAN07:12:00:00 0
AA 04JAN07:14:30:00 04JAN07:15:30:00 0
AA 04JAN07:15:30:00 04JAN07:16:30:00 0
;
run;

data want;
set have;
retain LastSellRet 0;
lagLastsell=lag(lastsell);
if lastsell ne 0 then LastSellRet=Lastsell;
else if lastsell=0 and lagLastsell ne 0 then LastSellRet=lagLastsell;
run;

proc print data=want;
run;

HTH
Patrick

Re: Repeat zero value with lagged value

deleted_user — Mon, 29 Mar 2010 13:06:05 GMT

I think my question was not clear. My data has already in SAS file as you can see in following link. Just additional data i wanna add.

http://images.temppic.com/29-03-2010/images_vertis/1269866814_0.84240500.jpg

Anyway,Thank you very much for your answer Patrick. Your help is always appreciated.