Error in log when calculate min value within a window

[hhchenfx]

Hi,

For a give day, I lookback 2 day and look forward 3 day and min value and the date of that min value occurs.

My code below seems to get the job done but it give error in log as below.

How can I fix it?

Thanks,

HHC

12744
12745 data want;
12746 set have;
12747 min=value;
12748 min_date=date;
12749 do n = _N_-2 to _N_+3 by 1;
12750 set have (keep = id date value rename=(id=id_1 date=date_1 value=value_1))point=n;
12751 if id_1 = id and value_1<min then do;
12752 min=value_1;
12753 min_date=date_1;
12754 end;
12755 end;
12756 run;

id=1 date=1 value=1 min=1 min_date=1 n=5 id_1=1 date_1=4 value_1=8 _ERROR_=1 _N_=1
id=1 date=2 value=3 min=1 min_date=1 n=6 id_1=1 date_1=5 value_1=9 _ERROR_=1 _N_=2
id=2 date=6 value=80 min=20 min_date=7 n=18 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=14
id=2 date=7 value=20 min=20 min_date=7 n=19 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=15
id=2 date=8 value=60 min=20 min_date=7 n=20 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=16
NOTE: There were 16 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 16 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

data have; 
input id date value;
datalines;
1 1 1
1 2 3
1 3 4
1 4 8
1 5 9
1 6 8
1 7 2
1 8 6
2 1 10
2 2 30
2 3 40
2 4 80
2 5 90
2 6 80
2 7 20
2 8 60
;run;

data want; 
set have;
min=value;
min_date=date;
do n = _N_-2 to _N_+3 by 1;
	set have (keep = id date value rename=(id=id_1 date=date_1 value=value_1))point=n;
	if id_1 = id and value_1<min then do;
		min=value_1;
		min_date=date_1;
	end;
end;
run;

[Kurt_Bremser]

This loop would never execute:

do n = _N_-2 to N+3 by 1;

Since N is missing when N+3 is calculated, the TO value will be missing.

I suspect this is not the code you really ran. Post the complete (all code and messages) log of your step by copy/pasting into a window opened with this button:

[hhchenfx]

You are right, the command should be: do n = _N_-2 to _N_+3 by 1;

Still there is an error shown below.

12745 data want;
12746 set have;
12747 min=value;
12748 min_date=date;
12749 do n = _N_-2 to _N_+3 by 1;
12750 set have (keep = id date value rename=(id=id_1 date=date_1 value=value_1))point=n;
12751 if id_1 = id and value_1<min then do;
12752 min=value_1;
12753 min_date=date_1;
12754 end;
12755 end;
12756 run;

id=1 date=1 value=1 min=1 min_date=1 n=5 id_1=1 date_1=4 value_1=8 _ERROR_=1 _N_=1
id=1 date=2 value=3 min=1 min_date=1 n=6 id_1=1 date_1=5 value_1=9 _ERROR_=1 _N_=2
id=2 date=6 value=80 min=20 min_date=7 n=18 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=14
id=2 date=7 value=20 min=20 min_date=7 n=19 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=15
id=2 date=8 value=60 min=20 min_date=7 n=20 id_1=2 date_1=8 value_1=60 _ERROR_=1 _N_=16
NOTE: There were 16 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 16 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

data have; 
input id date value;
datalines;
1 1 1
1 2 3
1 3 4
1 4 8
1 5 9
1 6 8
1 7 2
1 8 6
2 1 10
2 2 30
2 3 40
2 4 80
2 5 90
2 6 80
2 7 20
2 8 60
;run;

data want; 
set have;
min=value;
min_date=date;
do n = _N_-2 to _N_+3 by 1;
	set have (keep = id date value rename=(id=id_1 date=date_1 value=value_1))point=n;
	if id_1 = id and value_1<min then do;
		min=value_1;
		min_date=date_1;
	end;
end;
run;

[Kurt_Bremser]

Look at the values of _N_ in your ERROR messages. Your DO loop needs to take care of the edge cases at the beginning and end of the dataset. When _N_ =1, you try to read observations -1 and 0, which do not exist. Similar happens at the end, when you try to read obs 17, 18 and 19.

[Ksharp · Accepted solution]

data have; 
input id date value;
datalines;
1 1 1
1 2 3
1 3 4
1 4 8
1 5 9
1 6 8
1 7 2
1 8 6
2 1 10
2 2 30
2 3 40
2 4 80
2 5 90
2 6 80
2 7 20
2 8 60
;

data want;
 if _n_=1 then do;
   if 0 then set have;
   declare hash h();
   h.definekey('date');
   h.definedata('value');
   h.definedone();

   array d{6} _temporary_;
   array v{6} _temporary_;
 end;
h.clear();
call missing(of d{*} v{*});

do until(last.id);
 set have;
 by id;
 h.add();
end;



do until(last.id);
 set have;
 by id;
 old_value=value;
 n=0;

 do _date=date-2 to date+3;
  if h.find(key:_date)=0 then do;n+1;d{n}=_date;v{n}=value; end;
 end;
 min=min(of v{*});
 idx=whichn(min,of v{*});
 min_date=d{idx};

 output;
end;

drop value idx _date n;
run;

[hhchenfx]

Thank you for your help!

HHC