BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dac_js
Quartz | Level 8

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

4 REPLIES 4
Ksharp
Super User
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;
novinosrin
Tourmaline | Level 20

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;
dac_js
Quartz | Level 8
Thank you again.
Your code is working.
Is this possible to do it without using hash objects?

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Health and Life Sciences Learning

 

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.

LEARN MORE

Discussion stats
  • 4 replies
  • 1018 views
  • 2 likes
  • 3 in conversation