Compare row to next row based on multiple columns

sasuser_sk — Thu, 09 Sep 2021 20:44:59 GMT

Hello Everyone: Can anyone please help me figure out the following code in SAS! Thank you. I'm creating columns called Migration and Prior Migration using:

Migration:

if(Acct_No=Next(Acct_No) and Cur_Flg=0 and Next(StartDate)='2021/02/28' ) then "Migration"
if(Acct_No=Next(Acct_No) and Cur_Flg=0 and Name='Pearl 175' and Next(Name)='Pearl 300' ) then "Migration 175 to 300"

Prior Migration:

if(Acct_No=Next(Acct_No) and Prior(SCD_End_Date)='2021/03/01' and ProdStartDate='2021/02/28') then "Migration"

if(Acct_No=Next(Acct_No) and Name='Pearl 300' and Prior(Name)='Pearl 175' ) then "Migration 175 To 300"

Data:

Name Acct_ No ProdStartDate ProdEndDate SCD End Date
Beads 25 08996 5/13/2021 5/27/2023
Beads 25 10197 11/14/2019 11/27/2021 9/5/2021
Beads 50 11606 4/7/2020 4/21/2022
Pearls 80 11739 9/26/2019 10/9/2021 3/1/2021
Pearls 150 11739 2/28/2021 10/9/2021
Beads 50 11795 10/26/2020 11/9/2022
Beads 25 11802 2/10/2021 2/24/2023
Beads 25 11809 10/2/2020 10/16/2022
Beads 25 13445 7/13/2021 7/27/2023
Beads 25 86709 8/6/2020 8/20/2022
Pearls 80 13627 11/20/2020 12/4/2022 3/1/2021
Pearls 150 13627 2/28/2021 12/4/2022
Beads 50 13958 7/15/2021 7/29/2023
Beads 25 16457 5/12/2021 5/26/2023
Beads 50 88377 3/5/2020 3/19/2022 3/28/2020
Beads 50 88377 3/27/2020 3/19/2022
Beads 25 24601 5/20/2021 6/3/2023
Pearls 80 24636 11/16/2020 11/30/2022 3/1/2021
Pearls 150 24636 2/28/2021 11/30/2022 5/11/2021
Pearls 150 24636 2/28/2021 11/30/2022 7/27/2021
Beads 25 24636 7/26/2021 8/9/2023
Beads 25 25706 12/5/2020 12/19/2022
Beads 25 26474 9/21/2020 10/5/2022
Beads 25 18721 3/4/2021 3/18/2023
Pearls 175 26887 12/9/2019 12/22/2021 7/13/2020
Pearls 300 26887 7/12/2020 12/22/2021
Beads 50 27411 6/4/2020 6/18/2022 8/5/2021
Pearls 80 27460 1/15/2021 1/29/2023 3/1/2021
Pearls 150 27460 2/28/2021 1/29/2023
Beads 25 29956 2/16/2021 3/2/2023 5/21/2021
Beads 25 29956 5/20/2021 3/2/2023
Beads 25 31100 3/26/2021 4/9/2023
Beads 25 33991 12/19/2019 12/31/2021
Pearls 80 35606-00001 7/9/2020 7/23/2022 3/1/2021
Pearls 150 35606-00001 2/28/2021 7/23/2022
Beads 25 35942 4/21/2020 5/5/2022
Pearls 175 45873 12/20/2019 1/2/2022 7/13/2020
Pearls 300 45873 7/12/2020 1/2/2022
Beads 25 39923 9/17/2020 9/24/2021 12/2/2020
Beads 50 39923 12/1/2020 12/15/2022

Output Needed:

Name Acct_ No ProdStartDate ProdEndDate SCD End Date Migration Prior Migration
Beads 25 08996 5/13/2021 5/27/2023
Beads 25 10197 11/14/2019 11/27/2021 9/5/2021
Beads 50 11606 4/7/2020 4/21/2022
Pearls 80 11739 9/26/2019 10/9/2021 3/1/2021 Migration
Pearls 150 11739 2/28/2021 10/9/2021 Migration
Beads 50 11795 10/26/2020 11/9/2022
Beads 25 11802 2/10/2021 2/24/2023
Beads 25 11809 10/2/2020 10/16/2022
Beads 25 13445 7/13/2021 7/27/2023
Beads 25 86709 8/6/2020 8/20/2022
Pearls 80 13627 11/20/2020 12/4/2022 3/1/2021 Migration
Pearls 150 13627 2/28/2021 12/4/2022 Migration
Beads 50 13958 7/15/2021 7/29/2023
Beads 25 16457 5/12/2021 5/26/2023
Beads 50 88377 3/5/2020 3/19/2022 3/28/2020
Beads 50 88377 3/27/2020 3/19/2022
Beads 25 24601 5/20/2021 6/3/2023
Pearls 80 24636 11/16/2020 11/30/2022 3/1/2021 Migration
Pearls 150 24636 2/28/2021 11/30/2022 5/11/2021 Migration Migration
Pearls 150 24636 2/28/2021 11/30/2022 7/27/2021
Beads 25 24636 7/26/2021 8/9/2023
Beads 25 25706 12/5/2020 12/19/2022
Beads 25 26474 9/21/2020 10/5/2022
Beads 25 18721 3/4/2021 3/18/2023
Pearls 175 26887 12/9/2019 12/22/2021 7/13/2020 Migration 175 To 300
Pearls 300 26887 7/12/2020 12/22/2021 Migration 175 To 300
Beads 50 27411 6/4/2020 6/18/2022 8/5/2021
Pearls 80 27460 1/15/2021 1/29/2023 3/1/2021 Migration
Pearls 150 27460 2/28/2021 1/29/2023 Migration
Beads 25 29956 2/16/2021 3/2/2023 5/21/2021
Beads 25 29956 5/20/2021 3/2/2023
Beads 25 31100 3/26/2021 4/9/2023
Beads 25 33991 12/19/2019 12/31/2021
Pearls 80 35606-00001 7/9/2020 7/23/2022 3/1/2021 Migration
Pearls 150 35606-00001 2/28/2021 7/23/2022 Migration
Beads 25 35942 4/21/2020 5/5/2022
Pearls 175 45873 12/20/2019 1/2/2022 7/13/2020 Migration 175 To 300
Pearls 300 45873 7/12/2020 1/2/2022 Migration 175 To 300
Beads 25 39923 9/17/2020 9/24/2021 12/2/2020
Beads 50 39923 12/1/2020 12/15/2022

Re: Compare row to next row based on multiple columns

Shmuel — Thu, 09 Sep 2021 22:52:06 GMT

There is no function NEXT in sas but if you can sort the file to reverse order then you can use lag function to compare to the previous observation value of a variable.

If there is no a date variable to sort by descending, add new variable: sequence = _N_

and sort by descending sequence.

Re: Compare row to next row based on multiple columns

Tom — Fri, 10 Sep 2021 04:24:03 GMT

Do you actually have date values in those variables? If so then to specify a constant value you need to use a date constant not a string. Date constants have the letter d after the closing quote and the value inside the quotes has to be something the DATE informat can understand. So '28FEB2021'd not '2021/02/28' nor '2/28/2021'.

There is no "next" or "lead" function in a data step. There is a LAG() function so if you could sort the data in descending order it might work better.

If you really did want to implement a "lead" function here is a trick. Add an extra SET statement that re-reads the same input dataset but starts with the second observation. Use KEEP= to include the variables you want to "lead" and use RENAME= to give them new names. Also include one more empty observation so the number of observations are the same in the two SET statements.

If your data is grouped by (say by NAME or ACCT_NO or both) then use BY group processing to know when to ignore the values.

So something like this:

data want;
  set have;
  by acct_no;
  set have(firstobs=2 keep=startdate rename=(startdate=nextdate)) have(obs=1 drop=_all_);
  prior_scd_end_date=lag(SCD_End_Date);
  if first.acct_no then call missing(prior_scd_end_date);
  if last.acct_no then call missing(nextdate);
  .... code referencing PRIOR_SCD_END_DATE and NEXTDATE .... 
run;

topic Compare row to next row based on multiple columns in SAS Programming

Compare row to next row based on multiple columns

Re: Compare row to next row based on multiple columns

Re: Compare row to next row based on multiple columns