topic Re: Delete pair of observations based on positive and negative supply values in SAS Health and Life Sciences

Delete pair of observations based on positive and negative supply values

dac_js — Sun, 22 Nov 2020 10:18:27 GMT

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.

Re: Delete pair of observations based on positive and negative supply values

Ksharp — Sun, 22 Nov 2020 11:24:54 GMT

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;

Re: Delete pair of observations based on positive and negative supply values

novinosrin — Sun, 22 Nov 2020 13:34:14 GMT


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;

Re: Delete pair of observations based on positive and negative supply values

dac_js — Sun, 22 Nov 2020 14:26:49 GMT

Thank you again.
Your code is working.
Is this possible to do it without using hash objects?

Re: Delete pair of observations based on positive and negative supply values

dac_js — Sun, 22 Nov 2020 14:48:37 GMT

Thank you @Ksharp