Treating negative values

[Nimish_Vaddiparti]

Hi,

I have a few negative observations in my data and I want to replace them by the average of the pre & post values. I cannot use proc expand and hence, can you please suggest an alternate route?

Its urgent and hence, can you please get back asap?

E.g. of data:

Market,Product,Date,Price

X,ZZZ,12/10/2020,15

X,ZZZ,13/10/2020,17

X,ZZZ,14/10/2020,-15

X,ZZZ,15/10/2020,25

Regards,

Nimish

[Jagadishkatam]

Hi Nimish,

Please try below code, check want2 , hope it helps you

data have;

infile cards dlm=',';

input Market$ Product$ Date:ddmmyy10. Price;

if price <0 then price=.;

format date ddmmyy10.;

cards;

X,ZZZ,12/10/2020,15

X,ZZZ,13/10/2020,17

X,ZZZ,14/10/2020,-15

X,ZZZ,15/10/2020,25

;

proc sql;

create table want as select market,product,avg(price) as avg from have where price >0 group by market,product;

create table want2 as select a.*, coalesce(a.price,b.avg) as newprice from have as a left join want as b on a.market=b.market and a.product=b.product;

quit;

Thanks,

Jag

[Reeza]

When you say pre/post do you mean the value before and after the negative/missing value?

[Nimish_Vaddiparti]

Yes, Reeza

[Kurt_Bremser]

There's probably a very clever way to do it in sql, but I'm not much versed in that, so:

proc sort data=have;

by market product date;

run;

data int1; /* keep previous price */

set have;

oldprice = lag(price);

if first.product then oldprice = .; /* no previous price */

run;

proc sort data=int1;

by market product descending date;

run;

data int2; /* keep following price */

set int1;

by market product;

newprice = lag(price);

if first.product then newprice = .;

run;

data want (drop=newprice oldprice); /* calculate averages */

set int1;

if price < 0 then price = (newprice + oldprice) / 2;

run;

The steps could be optimized, but I kept each single step for clarity.