topic Re: Use proc sql to find min value in SAS Programming

Use proc sql to find min value

TammyGoSAS — Fri, 21 Feb 2014 10:28:26 GMT

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

Re: Use proc sql to find min value

LinusH — Fri, 21 Feb 2014 10:51:20 GMT

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

Re: Use proc sql to find min value

Barnipaz — Fri, 21 Feb 2014 15:22:50 GMT

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;

Re: Use proc sql to find min value

PGStats — Fri, 21 Feb 2014 15:26:05 GMT

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;

Re: Use proc sql to find min value

TammyGoSAS — Sat, 22 Feb 2014 05:00:12 GMT

Thank you so much, PG.