DATA Step, Macro, Functions and more

Selecting a customised row based on requirement

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Selecting a customised row based on requirement

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:

IDDate1Date2Date3
100103-Apr-17Dec-16Jul-03
100103-Apr-17Jan-17Jul-03
100103-Apr-17Feb-17Jul-03
100103-Apr-17Mar-17Jul-03
100103-Apr-17Apr-17Apr-17
100103-Apr-17May-17Apr-17
100103-Apr-17Jun-17Apr-17
100103-Apr-17Jul-17Apr-17
100103-Apr-17Aug-17Apr-17
100103-Apr-17Sep-17Apr-17
100103-Apr-17Oct-17Apr-17
100103-Apr-17Nov-17Apr-17
100103-Apr-17Dec-17Apr-17

 

Desired Output:

IDDate1Date2Date3
100103-Apr-17Mar-17Jul-03

Accepted Solutions
Solution
‎05-18-2018 04:43 PM
Super User
Posts: 6,629

Re: Selecting a customised row based on requirement

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;

 

View solution in original post


All Replies
Occasional Contributor
Posts: 18

Re: Selecting a customised row based on requirement

The condition is month(date1)=date3 actually.

Valued Guide
Posts: 531

Re: Selecting a customised row based on requirement

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.

Super User
Posts: 10,686

Re: Selecting a customised row based on requirement

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;
Super User
Super User
Posts: 9,407

Re: Selecting a customised row based on requirement

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;

    
Super User
Posts: 6,629

Re: Selecting a customised row based on requirement

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;

Occasional Contributor
Posts: 18

Re: Selecting a customised row based on requirement

Posted in reply to Astounding

 

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;

 

Super User
Posts: 6,629

Re: Selecting a customised row based on requirement

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?

Occasional Contributor
Posts: 18

Re: Selecting a customised row based on requirement

Posted in reply to Astounding

Yes, that's correct. thanks

Solution
‎05-18-2018 04:43 PM
Super User
Posts: 6,629

Re: Selecting a customised row based on requirement

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;

 

Occasional Contributor
Posts: 18

Re: Selecting a customised row based on requirement

Posted in reply to Astounding

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 188 views
  • 0 likes
  • 5 in conversation