Use proc sql to find min value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Use proc sql to find min value

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


Accepted Solutions
Solution
‎02-21-2014 10:26 AM
Respected Advisor
Posts: 4,659

Re: Use proc sql to find min value

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

PG

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Use proc sql to find min value

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

Data never sleeps
Contributor
Posts: 33

Re: Use proc sql to find min value

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;

Solution
‎02-21-2014 10:26 AM
Respected Advisor
Posts: 4,659

Re: Use proc sql to find min value

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

PG
Occasional Contributor
Posts: 15

Re: Use proc sql to find min value

Thank you so much, PG.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 411 views
  • 6 likes
  • 4 in conversation