BookmarkSubscribeRSS Feed
Nimish_Vaddiparti
Calcite | Level 5

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

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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
Reeza
Super User

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

Nimish_Vaddiparti
Calcite | Level 5

Yes, Reeza

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1024 views
  • 0 likes
  • 4 in conversation