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
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
When you say pre/post do you mean the value before and after the negative/missing value?
Yes, Reeza
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.