topic Re: wrangle data to compare previous record in SAS Programming

wrangle data to compare previous record

dennis_oz — Tue, 01 Jun 2021 03:14:25 GMT

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.

Re: wrangle data to compare previous record

mkeintz — Tue, 01 Jun 2021 05:49:13 GMT

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?

Re: wrangle data to compare previous record

dennis_oz — Tue, 01 Jun 2021 06:07:07 GMT

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

======

Re: wrangle data to compare previous record

Ksharp — Tue, 01 Jun 2021 12:15:23 GMT

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;

Re: wrangle data to compare previous record

dennis_oz — Tue, 01 Jun 2021 14:43:30 GMT

Many Thanks !! Your code works perfectly 🙂 !