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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

10 REPLIES 10
Vishy
Obsidian | Level 7

The condition is month(date1)=date3 actually.

jklaverstijn
Rhodochrosite | Level 12

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
Super User
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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Obsidian | Level 7

 

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
PROC Star

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
Obsidian | Level 7

Yes, that's correct. thanks

Astounding
PROC Star

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
Obsidian | Level 7

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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