Use proc sql to find min value

[TammyGoSAS]

Hello everyone,

I have a time series data with sale information and want to find non-zero min price in previous 5 minutes.

Input

Time  Price

9:00    10

9:05    11

9:06      0

9:07    13

9:09      0

9:12    15

Desired output

Time  Price  Lag_Min_Price

9:00    10             .

9:05    11           10

9:06      0           11

9:07    13           11

9:09      0           11

9:12    15           13

I try proc sql, but there is something wrong. The problem might be min(price > 0), but I don't know how to correct it. Does anyone have solution? Thank you.

proc sql;
     create table want as
     select A.*,
          (select min(price > 0)
           from have as B
           where A.time-5<=B.time<=A.time-1) as Lag_Min_Price

     from have as A;
quit;

Best,

Tammy

[LinusH]

I don't know if this resolve your whole requirement, but I would use the price > 0 in the sub-query where-clause instead.

[Barnipaz]

I've done with a datastep and a sort before.

proc sort data=price; by time;run;

data want (keep=price time min_price);

  set price;

  by time;

  _1=time-lag1(time);

  _2=time-lag2(time);

  _3=time-lag3(time);

  _4=time-lag4(time);

  _5=time-lag5(time);

  p_1=lag1(price);

  p_2=lag2(price);

  p_3=lag3(price);

  p_4=lag4(price);

  p_5=lag5(price);

  if _1=60 then do;

    if p_1 ne 0 then price1=p_1;

  end;

  if _1=120 then do;

      if p_1 ne 0 then price2=p_1;

  end;

  if _1=180 then do;

    if p_1 ne 0 then price3=p_1;

  end;

  if _1=240 then do;

    if p_1 ne 0 then price4=p_1;

  end;

  if _1=300 then do;

   if p_1 ne 0 then price5=p_1;

  end;

  if _2=120 then do;

    if p_2 ne 0 then price2=p_2;

  end;

  if _2=180 then do;

    if p_2 ne 0 then price3=p_2;

  end;

  if _2=240 then do;

    if p_2 ne 0 then price4=p_2;

  end;

  if _2=300 then do;

   if p_2 ne 0 then price5=p_2;

  end;

  if _3=180 then do;

    if p_3 ne 0 then price3=p_3;

  end;

  if _3=240 then do;

    if p_3 ne 0 then price4=p_3;

  end;

  if _3=300 then do;

   if p_3 ne 0 then price5=p_3;

  end;

  if _4=240 then do;

    if p_4 ne 0 then price4=p_4;

  end;

  if _4=300 then do;

   if p_4 ne 0 then price5=p_4;

  end;

  if _5=300 then do;

   if p_5 ne 0 then price5=p_5;

  end;

  min_price=min(of price1-price5);

  run;

[PGStats · Accepted solution]

Try this:

data have;

input Time :time5.  Price;

format time time5.;

datalines;

9:00    10

9:05    11

9:06      0

9:07    13

9:09      0

9:12    15

;

proc sql;

create table want as

select A.*,

  (select min(price)

   from have

   where price > 0 and time between A.time-300 and A.time) as Lag_Min_Price

from have as A;

select * from want;

quit;

PG

[TammyGoSAS]

Thank you so much, PG.