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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

New Learning Events in April

 

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.

LEARN MORE

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