Selecting a customised row based on requirement

[Vishy]

How can I select the row I needed based on the condition? Below is the requirement. When date1=date3 then I need the row which has previous month date of date2. In the below example.

Data:

ID	Date1	Date2	Date3
1001	03-Apr-17	Dec-16	Jul-03
1001	03-Apr-17	Jan-17	Jul-03
1001	03-Apr-17	Feb-17	Jul-03
1001	03-Apr-17	Mar-17	Jul-03
1001	03-Apr-17	Apr-17	Apr-17
1001	03-Apr-17	May-17	Apr-17
1001	03-Apr-17	Jun-17	Apr-17
1001	03-Apr-17	Jul-17	Apr-17
1001	03-Apr-17	Aug-17	Apr-17
1001	03-Apr-17	Sep-17	Apr-17
1001	03-Apr-17	Oct-17	Apr-17
1001	03-Apr-17	Nov-17	Apr-17
1001	03-Apr-17	Dec-17	Apr-17

Desired Output:

ID	Date1	Date2	Date3
1001	03-Apr-17	Mar-17	Jul-03

[Vishy]

The condition is month(date1)=date3 actually.

[jklaverstijn]

Can we interpret your question as "if date1=date3 then output the previous row"? That seems to work (date2 has the previous month) but that may be a concidence in the data. But if so, this might work:

data want;
 set have;
 set have (firstobs=2 rename=(id=id2 date1=date1_2 date2=date2_2 date3=date3_2));
 keep id date1 date2 date3;

 if date1_2=date3_2 then
  output;
run;

I must say that in your example the different formatting of date1 and date3 is confusing. If they are strings than date1 will never equal date3.

Hope this helps,

-- Jan.

[Ksharp]

data have;
infile cards expandtabs;
input (ID	Date1	Date2	Date3) (: $20.);
cards;
1001	03-Apr-17	Dec-16	Jul-03
1001	03-Apr-17	Jan-17	Jul-03
1001	03-Apr-17	Feb-17	Jul-03
1001	03-Apr-17	Mar-17	Jul-03
1001	03-Apr-17	Apr-17	Apr-17
1001	03-Apr-17	May-17	Apr-17
1001	03-Apr-17	Jun-17	Apr-17
1001	03-Apr-17	Jul-17	Apr-17
1001	03-Apr-17	Aug-17	Apr-17
1001	03-Apr-17	Sep-17	Apr-17
1001	03-Apr-17	Oct-17	Apr-17
1001	03-Apr-17	Nov-17	Apr-17
1001	03-Apr-17	Dec-17	Apr-17
;
run;
data temp;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
data x;
 set temp;
 by id;
 retain found;
 if first.id then found=0;
 if substr(date1,4)=date3 and not found then do;nn=n-1;found=1;output;end;
 keep nn;
run;
proc sql;
select *
 from temp
  where n in (select nn from x);
quit;

[RW9]

Post test data in the form of a datastep!

What are those variables, character?  If so you would need to convert them or do some string comparison.  The rough logic, is hold current record over to next, then do check and only output then e.g.

data want;
  set have;
  retain lstd1 lstd2 lstd3;
  by id;
  if first.id then do;
    lstd1=date1;
    lstd2=date2;
    lstd3=date3;
  end;
  else do;
    if index(date3,date1) > 0 then output;
    else do;
      lstd1=date1;
      lstd2=date2;
      lstd3=date3;
    end;
  end;
run;

    

[Astounding]

So how do you tell when date1=date3?  The variables don't appear to contain the same values.  Since we have no inside knowledge about what is really in these variables, I'm just going to assume that we can look for date1=date3 and that will magically find the proper observations.  But that could be just plain wrong and you may need to supply much more information.

data want;

set have;

if date1=date3 and _n_ > 1;

recnum = _n_ - 1;

set have point=recnum;

output;

run;

[Vishy]

　

Actually if month & year of Date1 = month & year of date3 then I need the row with previous month's of date2. In the below dataset I need the row4.

date1=Apr17 & date3=Apr17 so I need the previous month row which is date2=Mar17

data check1;

input row ID date1 :date7. date2 :anydtdte. date3 :anydtdte.;

format row 8. date1 date9. date2 monyy5. date3 monyy5. ;

datalines;

1 1001 03Apr17 Dec16 Jul03

2 1001 03Apr17 Jan17 Jul03

3 1001 03Apr17 Feb17 Jul03

4 1001 03Apr17 Mar17 Jul03

5 1001 03Apr17 Apr17 Apr17

6 1001 03Apr17 May17 Apr17

7 1001 03Apr17 Jun17 Apr17

8 1001 03Apr17 Jul17 Apr17

9 1001 03Apr17 Aug17 Apr17

10 1001 03Apr17 Sep17 Apr17

11 1001 03Apr17 Oct17 Apr17

12 1001 03Apr17 Nov17 Apr17

13 1001 03Apr17 Dec17 Apr17

;

run;

[Astounding]

So just to clarify ... you have a match on 9 of the observations.  But you only want to output one observation to account for all of these matches.  Does that sound right?

[Vishy]

Yes, that's correct. thanks

[Astounding · Accepted solution]

OK, easiest is probably to get all 9 matches and then remove duplicates after the fact:

data want;

set have;

if _n_ > 1;

if year(date1) = year(date3) and month(date1) = month(date3);

look_for = intnx('month', date1, -1);

do recnum = _n_ - 1 to 1 by -1 until (look_for = intnx('month', date2, 0));

   set have point=recnum;

   if look_for = intnx('month', date2, 0) then output;

end;

run;

proc sort data=want nodupkey;

by date1 date2 date3;

run;

[Vishy]

Thank you all for contributing your thoughts on this. It really helps.