data a1;
input ID date :date9. Supply;
format date date9.;
datalines;
1 03JAN19 90
2 02JAN19 30
2 15FEB2019 30
3 02JAN19 30
3 05JAN19 -30
3 30MAR19 30
4 03APR19 60
5 01FEB19 60
5 02FEB19 60
5 06FEB19 -60
6 01MAR19 30
6 01APR19 30
;
I am trying to delete pairs of observations where a negative value of supply appears within 7days of a positive value (the FIRST AND SECOND OBSERVATION of ID 3 and SECOND AND THIRD OBSERVATION of ID 5 in this example), considering the second observations are actually a refund of the earlier observation.
I have a code which is deleting all observations in a 7day window. In this example the code is deleting FIRST, SECOND AND THIRD OBSERVATIONS of ID 5. I need a code that deletes only the positive-negative pairs (to keep the first observation for ID 5).
Thanks in advance for the help.
data a1;
input ID date :date9. Supply;
format date date9.;
datalines;
1 03JAN19 90
2 02JAN19 30
2 15FEB2019 30
3 02JAN19 30
3 05JAN19 -30
3 30MAR19 30
4 03APR19 60
5 01FEB19 60
5 02FEB19 60
5 06FEB19 -60
6 01MAR19 30
6 01APR19 30
;
data want;
if _n_=1 then do;
dcl hash h();
h.definekey('supply');
h.definedata('_iorc_');
h.definedone();
end;
array t(-138061:2936547) _temporary_;
call missing(of t(*),_iorc_);
do _n_=1 by 1 until(last.id);
set a1;
by id;
_iorc_=date;
if sign(supply)=1 then h.replace();
else if h.find(key:abs(supply))=0 then if intck('day',_iorc_,date)<=7 then do;
t(date)=date;
t(_iorc_)=_iorc_;
end;
end;
do _n_=1 to _n_;
set a1;
if t(date) then continue;
output;
end;
h.clear();
run;
data a1; input ID date :date9. Supply; format date date9.; datalines; 1 03JAN19 90 2 02JAN19 30 2 15FEB2019 30 3 02JAN19 30 3 05JAN19 -30 3 30MAR19 30 4 03APR19 60 5 01FEB19 60 5 02FEB19 60 5 06FEB19 -60 6 01MAR19 30 6 01APR19 30 ; data have; set a1; n+1; run; data temp; set have; if id=lag(id) and dif(date)<=7 and supply<0 and supply=-lag(supply) ; keep n; run; data n; set temp; output; n=n-1;output; run; proc sql; create table want(drop=n) as select * from have where n not in (select n from n); quit;
data a1;
input ID date :date9. Supply;
format date date9.;
datalines;
1 03JAN19 90
2 02JAN19 30
2 15FEB2019 30
3 02JAN19 30
3 05JAN19 -30
3 30MAR19 30
4 03APR19 60
5 01FEB19 60
5 02FEB19 60
5 06FEB19 -60
6 01MAR19 30
6 01APR19 30
;
data want;
if _n_=1 then do;
dcl hash h();
h.definekey('supply');
h.definedata('_iorc_');
h.definedone();
end;
array t(-138061:2936547) _temporary_;
call missing(of t(*),_iorc_);
do _n_=1 by 1 until(last.id);
set a1;
by id;
_iorc_=date;
if sign(supply)=1 then h.replace();
else if h.find(key:abs(supply))=0 then if intck('day',_iorc_,date)<=7 then do;
t(date)=date;
t(_iorc_)=_iorc_;
end;
end;
do _n_=1 to _n_;
set a1;
if t(date) then continue;
output;
end;
h.clear();
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Need courses to help you with SAS Life Sciences Analytics Framework, SAS Health Cohort Builder, or other topics? Check out the Health and Life Sciences learning path for all of the offerings.