topic Re: Comparing date within rows in SAS Programming

topic Re: Comparing date within rows in SAS Programming https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960230#M374530 <P>The data step creating have has errors: unclosed quotes and a run in the middle of the step. The format statement is missing, too.</P> <P> </P> <P>Saving the first values of <FONT face="courier new,courier">police_effective_date</FONT> and <FONT face="courier new,courier">police_end_date</FONT> in retained variables and using them to set flag when the data step reached to last obs for <FONT face="courier new,courier">police</FONT> seem to create what you want:</P> <PRE><CODE class=" language-sas">data want; set have; by police; length flag $ 10 first_eff_date first_end_date 8 ; retain first_:; if first.police then do; first_eff_date = police_effective_date; first_end_date = police_end_date; end; if last.police then do; if police_end_date > first_end_date then do; if police_effective_date = first_eff_date then flag = "EXTENDED"; if police_effective_date > first_eff_date then flag = "SHIFTED"; end; end; drop first_:; run;</CODE></PRE> <P>Assuming the data is sorted by <FONT face="courier new,courier">police</FONT> and <FONT face="courier new,courier">end</FONT>.</P> Wed, 26 Feb 2025 12:00:23 GMT andreas_lds 2025-02-26T12:00:23Z Comparing date within rows https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960227#M374528 <P>Comparing date within rows</P><P> </P><P>I wish to check and compare dates within rows for a group of data. Also, it is possible to only have 1 row of record for 1 group.</P><P>data have;</P><P>POLICE='POL1';</P><P>END=0;</P><P>POLICE_EFFECTIVE_DATE='01Jan2024'd;</P><P>POLICE_END_DATE='31Dec2024'd;</P><P>output;</P><P> </P><P>POLICE='POL1';</P><P>END=1;</P><P>POLICE_EFFECTIVE_DATE='01Jan2024'd;</P><P>POLICE_END_DATE='31Dec2024'd;</P><P>output;</P><P> </P><P>POLICE='POL1';</P><P>END=2;</P><P>POLICE_EFFECTIVE_DATE='01Jan2024'd;</P><P>POLICE_END_DATE='31Jan2025'd;</P><P>output;</P><P>run;</P><P> </P><P>POLICE='POL2';</P><P>END=0;</P><P>POLICE_EFFECTIVE_DATE='01Jan2024'd;</P><P>POLICE_END_DATE='31Dec2024'd;</P><P>output;</P><P> </P><P>POLICE='POL3;</P><P>END=0;</P><P>POLICE_EFFECTIVE_DATE='01Jan2024'd;</P><P>POLICE_END_DATE='31Dec2024'd;</P><P>output;</P><P> </P><P>POLICE='POL3;</P><P>END=1;</P><P>POLICE_EFFECTIVE_DATE='01Jan2024'd;</P><P>POLICE_END_DATE='31Dec2024'd;</P><P>output;</P><P> </P><P>POLICE='POL3;</P><P>END=2;</P><P>POLICE_EFFECTIVE_DATE='01Feb2024'd;</P><P>POLICE_END_DATE='31Dec2025'd;</P><P>output;</P><P>It will look like this:<BR /><BR /></P><TABLE><TBODY><TR><TD>POLICE</TD><TD>END</TD><TD>POLICE_EFFECTIVE_DATE</TD><TD>POLICE_END_DATE</TD></TR><TR><TD>POL1</TD><TD>0</TD><TD>1/1/2024</TD><TD>31/12/2024</TD></TR><TR><TD>POL1</TD><TD>1</TD><TD>1/1/2024</TD><TD>31/12/2024</TD></TR><TR><TD>POL1</TD><TD>2</TD><TD>1/1/2024</TD><TD>31/1/2025</TD></TR><TR><TD>POL2</TD><TD>0</TD><TD>1/1/2024</TD><TD>31/12/2024</TD></TR><TR><TD>POL3</TD><TD>0</TD><TD>1/1/2024</TD><TD>31/12/2024</TD></TR><TR><TD>POL3</TD><TD>1</TD><TD>1/1/2024</TD><TD>31/12/2024</TD></TR><TR><TD>POL3</TD><TD>2</TD><TD>1/2/2024</TD><TD>31/1/2025</TD></TR></TBODY></TABLE><P> </P><P>What i want to achieve is this:</P><TABLE><TBODY><TR><TD>POLICE</TD><TD>END</TD><TD>POLICE_EFFECTIVE_DATE</TD><TD>POLICE_END_DATE</TD><TD>FLAG</TD></TR><TR><TD>POL1</TD><TD>0</TD><TD>1/1/2024</TD><TD>31/12/2024</TD><TD> </TD></TR><TR><TD>POL1</TD><TD>1</TD><TD>1/1/2024</TD><TD>31/12/2024</TD><TD> </TD></TR><TR><TD>POL1</TD><TD>2</TD><TD>1/1/2024</TD><TD>31/1/2025</TD><TD>EXTENDED</TD></TR><TR><TD>POL2</TD><TD>0</TD><TD>1/1/2024</TD><TD>31/12/2024</TD><TD> </TD></TR><TR><TD>POL3</TD><TD>0</TD><TD>1/1/2024</TD><TD>31/12/2024</TD><TD> </TD></TR><TR><TD>POL3</TD><TD>1</TD><TD>1/1/2024</TD><TD>31/12/2024</TD><TD> </TD></TR><TR><TD>POL3</TD><TD>2</TD><TD>1/2/2024</TD><TD>31/1/2025</TD><TD>SHIFTED</TD></TR></TBODY></TABLE><P><BR /><BR />From what i can tell, ""FLAG" can be defined by looking at the last.END POLICE_EFFECTIVE_DATE and POLICE_END_DATE.</P><P>If last.END POLICE_EFFECTIVE_DATE is the same as first.POLICE_EFFECTIVE_DATE  <BR />BUT<BR />last.POLICE_END_DATE is greater than first.POLICE_END_DATE then last.flag = 'EXTENDED'.</P><P> </P><P>If last.END POLICE_EFFECTIVE_DATE is greater than first.POLICE_EFFECTIVE_DATE  <BR />AND<BR />last.POLICE_END_DATE is greater than first.POLICE_END_DATE then last.flag = 'SHIFTED'.</P><P> </P><P>i can think of first sorting the dataset by POLICE and END. <BR />Then, without a complex loop, is there a way for me to compare the date based on POLICE and END and check if it is EXTENDED OR SHIFTED  or not.</P> Wed, 26 Feb 2025 11:16:35 GMT https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960227#M374528 StickyRoll 2025-02-26T11:16:35Z Re: Comparing date within rows https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960228#M374529 <P>From what I understand your pseudo code was already very close to actual SAS syntax. You just need to retain the values of the first row per group so you can compare it when you've reached the last row of the group. </P> <PRE><CODE class=" language-sas">data have; input POLICE $ END POLICE_EFFECTIVE_DATE :date9. POLICE_END_DATE :date9.; format POLICE_EFFECTIVE_DATE POLICE_END_DATE date9.; datalines; POL1 0 01JAN2024 31DEC2024 POL1 1 01JAN2024 31DEC2024 POL1 2 01JAN2024 31JAN2025 POL2 0 01JAN2024 31DEC2024 POL3 0 01JAN2024 31DEC2024 POL3 1 01JAN2024 31DEC2024 POL3 2 01FEB2024 31DEC2025 ; run; /* proc sort data=have; */ /* by POLICE POLICE_EFFECTIVE_DATE POLICE_END_DATE; */ /* run; */ data want(drop=first_:); set have; by POLICE POLICE_EFFECTIVE_DATE POLICE_END_DATE; retain first_POLICE_EFFECTIVE_DATE first_POLICE_END_DATE; length type $8; if first.POLICE then do; first_POLICE_EFFECTIVE_DATE =POLICE_EFFECTIVE_DATE; first_POLICE_END_DATE =POLICE_END_DATE; end; if last.POLICE then do; if POLICE_EFFECTIVE_DATE = first_POLICE_EFFECTIVE_DATE and POLICE_END_DATE > first_POLICE_END_DATE then type='EXTENDED'; else If POLICE_EFFECTIVE_DATE > first_POLICE_EFFECTIVE_DATE and POLICE_END_DATE > first_POLICE_END_DATE then type = 'SHIFTED'; end; run; proc print data=want; run; </CODE></PRE> <P> <span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Patrick_0-1740571223906.png" style="width: 400px;"><img src="https://communities.sas.com/t5/image/serverpage/image-id/104947i3F122DD09156702F/image-size/medium?v=v2&px=400" role="button" title="Patrick_0-1740571223906.png" alt="Patrick_0-1740571223906.png" /></span></P> <P> </P> Wed, 26 Feb 2025 12:00:35 GMT https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960228#M374529 Patrick 2025-02-26T12:00:35Z Re: Comparing date within rows https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960230#M374530 <P>The data step creating have has errors: unclosed quotes and a run in the middle of the step. The format statement is missing, too.</P> <P> </P> <P>Saving the first values of <FONT face="courier new,courier">police_effective_date</FONT> and <FONT face="courier new,courier">police_end_date</FONT> in retained variables and using them to set flag when the data step reached to last obs for <FONT face="courier new,courier">police</FONT> seem to create what you want:</P> <PRE><CODE class=" language-sas">data want; set have; by police; length flag $ 10 first_eff_date first_end_date 8 ; retain first_:; if first.police then do; first_eff_date = police_effective_date; first_end_date = police_end_date; end; if last.police then do; if police_end_date > first_end_date then do; if police_effective_date = first_eff_date then flag = "EXTENDED"; if police_effective_date > first_eff_date then flag = "SHIFTED"; end; end; drop first_:; run;</CODE></PRE> <P>Assuming the data is sorted by <FONT face="courier new,courier">police</FONT> and <FONT face="courier new,courier">end</FONT>.</P> Wed, 26 Feb 2025 12:00:23 GMT https://communities.sas.com/t5/SAS-Programming/Comparing-date-within-rows/m-p/960230#M374530 andreas_lds 2025-02-26T12:00:23Z