Treating negative values

Reply
Occasional Contributor
Posts: 18

Treating negative values

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

Trusted Advisor
Posts: 1,137

Re: Treating negative values

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

Thanks,
Jag
Super User
Posts: 19,023

Re: Treating negative values

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

Occasional Contributor
Posts: 18

Re: Treating negative values

Yes, Reeza

Super User
Posts: 7,386

Re: Treating negative values

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 178 views
  • 0 likes
  • 4 in conversation