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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.