wrangle data to compare previous record

[dennis_oz]

Hi friends ,

I was wanting some help on the below  . I need to compare the Service_Dates and if the Service date is present in the above record create an Flag='Y'.

My input data is attached as an excel 

Alternatively, the input code in a data step:

DATA WORK.check3;
     INPUT
        id $1-8  SERVICE_DATES $10-69 cntr $70-72 ;
		datalines;
267005668 20210401 || 20210401 || 20210401 || 20210401 || 20210401    3
267005668 20210401 || 20210401 || 20210401 || 20210401	              3
245060165 20200512 || 20200512	                                      9
245060165 20191212 || 20191212 || 20191212 || 20191212 || 20191212	  9
241777762 20210407                                                    10
241777762 20210407 || 20210407 || 20210407 || 20210407	              10
RUN;

Please can you help with my query .

Kind Regards.

[mkeintz]

Do you always have non-overlapping pairs of records to compare?  I.e., do you only compare record 2 to 1, 4 to 3, 6 to 5, but not 3 to 2, 5 to 4, etc.

Also why are you reading all your date variables as one long string?  Why not read each date value as a separate variable?

And finally, it seems that within each record containing multiple dates, those dates are always the same.  Is that the case for your entire dataset?

[dennis_oz]

hi @mkeintz  ,

this is the way I am receiving the data . 

I have further wrangled the data into below . I want to check now if a value in service_dates occurs in service_dates_new . If so just create a new column flag .

Please see my desied output in the last screen shot under output 

input code 

DATA WORK.check3;
     INPUT
        id $1-9  SERVICE_DATES $10-69 cntr $70-72  SERVICE_DATES_NEW $74-130;
		datalines;
267005668 20210401 || 20210401 || 20210401 || 20210423 || 20210401    1   20210423 
245060165 20200512 || 20200512	                                      2   20191212 || 20191212 || 20191212 || 20191212 || 20191212
241777762 20210407                                                    3  20210407 || 20210407 || 20210407 || 20210407	
RUN;

output

======

[Ksharp · Accepted solution]

DATA WORK.check3;
     INPUT
        id $1-8  SERVICE_DATES $10-69 cntr $70-72 ;
		datalines;
267005668 20210401 || 20210401 || 20210401 || 20210401 || 20210401    3
267005668 20210401 || 20210401 || 20210401 || 20210401	              3
245060165 20200512 || 20200512	                                      9
245060165 20191212 || 20191212 || 20191212 || 20191212 || 20191212	  9
241777762 20210407                                                    10
241777762 20210407 || 20210407 || 20210407 || 20210407	              10
;
RUN;

data want;
 set check3;
 lag=lag(SERVICE_DATES);
 if id=lag(id) then do;
   flag='Y';
   do i=1 to countw(SERVICE_DATES,'|');
     temp=scan(SERVICE_DATES,i,'|');
	 if not find(lag,strip(temp)) then do;
       flag=' ';leave;
	 end;
   end;
 end;
drop lag temp i;
run;

[dennis_oz]

Many Thanks !! Your code works perfectly 🙂 !