topic Treating negative values in SAS Programming

Treating negative values

Nimish_Vaddiparti — Wed, 04 Mar 2015 00:17:33 GMT

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

Re: Treating negative values

Jagadishkatam — Wed, 04 Mar 2015 00:57:31 GMT

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

Re: Treating negative values

Reeza — Wed, 04 Mar 2015 03:32:04 GMT

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

Re: Treating negative values

Nimish_Vaddiparti — Wed, 04 Mar 2015 08:26:21 GMT

Yes, Reeza

Re: Treating negative values

Kurt_Bremser — Wed, 04 Mar 2015 09:22:01 GMT

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.