Formula Help

[sasuser_sk]

Hello All - I'm trying to convert this Hyperion formula to SAS. I am creating column Migration using this formula. Can someone please help. Thank you!

if(Acct_No==Next(Acct_No) AND Flg==0 AND Next(StartDate)=='2021-02-28' ) then "Migration"
if(Acct_No==Next(Acct_No) AND Flg==0 && Name=='PROD 175' AND Next(Name)=='PROD 300' ) then "Migration 175 To 300"

Acct No StartDate Name Migration Prior Migration First Prod Start Date EndDate Flg
30065 2021-03-09 PROD EXTENDED 25 3/9/2021 3/23/2023 1
28772 2021-11-05 PROD 300 11/5/2021 11/19/2023 1
30089 2020-09-15 PROD EXTENDED 25 9/15/2020 9/29/2022 1
28598 2020-07-11 PROD 175 Migration 175 To 300 7/11/2020 7/25/2022 0
28598 2020-10-07 PROD 300 Migration 175 To 300 7/11/2020 10/21/2022 0
28598 2021-09-30 PROD 300 7/11/2020 10/21/2022 1
28689 2021-02-24 PROD EXTENDED 50 2/24/2021 3/10/2023 1
28760 2019-11-26 PROD 175 Migration 175 To 300 11/26/2019 12/9/2021 0
28760 2020-07-12 PROD 300 Migration 175 To 300 11/26/2019 12/9/2021 1
28852 2020-11-05 PROD 80 Migration 11/5/2020 11/19/2022 0
28852 2021-02-28 PROD 150 Migration 11/5/2020 11/19/2022 1
29043 2020-07-16 PROD EXTENDED 50 7/16/2020 7/29/2022 1
29192 2021-07-13 PROD EXTENDED 25 7/13/2021 7/27/2023 1
29553 2020-01-17 PROD 80 Migration 1/17/2020 1/30/2022 0
29553 2021-02-28 PROD 150 Migration 1/17/2020 1/30/2022 1
29324 2021-02-03 PROD EXTENDED 25 2/3/2021 2/17/2023 0
29324 2021-10-21 PROD EXTENDED 25 2/3/2021 2/17/2023 1
29325 2021-05-04 PROD 150 5/4/2021 5/18/2023 1
29591 2020-02-19 PROD 175 Migration 175 To 300 2/19/2020 3/4/2022 0
29591 2020-07-12 PROD 300 Migration 175 To 300 2/19/2020 3/4/2022 1

[Reeza]

What tool/system are you using?
EG, SAS DI Studio, SAS Studio? Are you coding using SQL or Data steps?

[sasuser_sk]

I am writing program code within SAS EG.

[Reeza]

Can you please provide your data as a data step then?
Please include two versions, one is the input data and the second is the expected output.

SAS doesn't do LEAD/NEXT functions well but there are other ways to get around it, usually by using BY group processing or a merge with the data itself. Seeing a full example will help us to assist you better.

[sasuser_sk]

data make4;
input acct 1-5 Flg 6-7 +1 startdate yymmdd10. +1 enddate yymmdd10. Name $char18.;
format startdate enddate yymmdd10.;
datalines;
30065 1 2021-03-09 2023-03-23 PROD EXTENDED 25
28772 1 2021-11-05 2023-11-19 PROD 300
30089 1 2020-09-15 2022-09-29 PROD EXTENDED 25
28598 0 2020-07-11 2022-07-25 PROD 175
28598 0 2020-10-07 2022-10-21 PROD 300
28598 1 2021-09-30 2022-10-21 PROD 300
28689 1 2021-02-24 2023-03-10 PROD EXTENDED 50
28760 0 2019-11-26 2021-12-09 PROD 175
28760 1 2020-07-12 2021-12-09 PROD 300
28852 0 2020-11-05 2022-11-19 PROD 80
28852 1 2021-02-28 2022-11-19 PROD 150
29043 1 2020-07-16 2022-07-29 PROD EXTENDED 50
29192 1 2021-07-13 2023-07-27 PROD EXTENDED 25
29553 0 2020-01-17 2022-01-30 PROD 80
29553 1 2021-02-28 2022-01-30 PROD 150
29324 0 2021-02-03 2023-02-17 PROD EXTENDED 25
29324 1 2021-10-21 2023-02-17 PROD EXTENDED 25
29325 1 2021-05-04 2023-05-18 PROD 150
29591 0 2020-02-19 2022-03-04 PROD 175
29591 1 2020-07-12 2022-03-04 PROD 300
;
run;

Desired Output: Create column Migration. 

acct	Flg	StartDate	EndDate	Name	Migration
30065	1	2021-03-09	2023-03-23	PROD EXTENDED 25
28772	1	2021-11-05	2023-11-19	PROD 300
30089	1	2020-09-15	2022-09-29	PROD EXTENDED 25
28598	0	2020-07-11	2022-07-25	PROD 175	Migration 175 To 300
28598	0	2020-10-07	2022-10-21	PROD 300
28598	1	2021-09-30	2022-10-21	PROD 300
28689	1	2021-02-24	2023-03-10	PROD EXTENDED 50
28760	0	2019-11-26	2021-12-09	PROD 175	Migration 175 To 300
28760	1	2020-07-12	2021-12-09	PROD 300
28852	0	2020-11-05	2022-11-19	PROD 80	Migration
28852	1	2021-02-28	2022-11-19	PROD 150
29043	1	2020-07-16	2022-07-29	PROD EXTENDED 50
29192	1	2021-07-13	2023-07-27	PROD EXTENDED 25
29553	0	2020-01-17	2022-01-30	PROD 80	Migration
29553	1	2021-02-28	2022-01-30	PROD 150
29324	0	2021-02-03	2023-02-17	PROD EXTENDED 25
29324	1	2021-10-21	2023-02-17	PROD EXTENDED 25
29325	1	2021-05-04	2023-05-18	PROD 150
29591	0	2020-02-19	2022-03-04	PROD 175	Migration 175 To 300
29591	1	2020-07-12	2022-03-04	PROD 300

[Tom]

Looks to me like you are trying to put the MIGRATION value on the wrong observation.  It is much easier in SAS to look back than look ahead.

data have;
  input acct Flg (startdate enddate) (:yymmdd.) Name $18.;
  format startdate enddate yymmdd10.;
datalines;
30065 1 2021-03-09 2023-03-23 PROD EXTENDED 25
28772 1 2021-11-05 2023-11-19 PROD 300
30089 1 2020-09-15 2022-09-29 PROD EXTENDED 25
28598 0 2020-07-11 2022-07-25 PROD 175
28598 0 2020-10-07 2022-10-21 PROD 300
28598 1 2021-09-30 2022-10-21 PROD 300
28689 1 2021-02-24 2023-03-10 PROD EXTENDED 50
28760 0 2019-11-26 2021-12-09 PROD 175
28760 1 2020-07-12 2021-12-09 PROD 300
28852 0 2020-11-05 2022-11-19 PROD 80
28852 1 2021-02-28 2022-11-19 PROD 150
29043 1 2020-07-16 2022-07-29 PROD EXTENDED 50
29192 1 2021-07-13 2023-07-27 PROD EXTENDED 25
29553 0 2020-01-17 2022-01-30 PROD 80
29553 1 2021-02-28 2022-01-30 PROD 150
29324 0 2021-02-03 2023-02-17 PROD EXTENDED 25
29324 1 2021-10-21 2023-02-17 PROD EXTENDED 25
29325 1 2021-05-04 2023-05-18 PROD 150
29591 0 2020-02-19 2022-03-04 PROD 175
29591 1 2020-07-12 2022-03-04 PROD 300
;

proc sort;
  by acct startdate ;
run;

data want;
  set have ;
  by acct ;
  length migration $40.;
  lag_name=lag(name);
  if first.acct then lag_name=' ';
  else if lag_name ne name then migration=catx(' ','Migration',scan(lag_name,-1,' '),'to',scan(name,-1,' '));
  drop lag_name;
run;

Results

Obs     acct    Flg     startdate       enddate    Name                     migration

  1    28598     0     2020-07-11    2022-07-25    PROD 175
  2    28598     0     2020-10-07    2022-10-21    PROD 300            Migration 175 to 300
  3    28598     1     2021-09-30    2022-10-21    PROD 300
  4    28689     1     2021-02-24    2023-03-10    PROD EXTENDED 50
  5    28760     0     2019-11-26    2021-12-09    PROD 175
  6    28760     1     2020-07-12    2021-12-09    PROD 300            Migration 175 to 300
  7    28772     1     2021-11-05    2023-11-19    PROD 300
  8    28852     0     2020-11-05    2022-11-19    PROD 80
  9    28852     1     2021-02-28    2022-11-19    PROD 150            Migration 80 to 150
 10    29043     1     2020-07-16    2022-07-29    PROD EXTENDED 50
 11    29192     1     2021-07-13    2023-07-27    PROD EXTENDED 25
 12    29324     0     2021-02-03    2023-02-17    PROD EXTENDED 25
 13    29324     1     2021-10-21    2023-02-17    PROD EXTENDED 25
 14    29325     1     2021-05-04    2023-05-18    PROD 150
 15    29553     0     2020-01-17    2022-01-30    PROD 80
 16    29553     1     2021-02-28    2022-01-30    PROD 150            Migration 80 to 150
 17    29591     0     2020-02-19    2022-03-04    PROD 175
 18    29591     1     2020-07-12    2022-03-04    PROD 300            Migration 175 to 300
 19    30065     1     2021-03-09    2023-03-23    PROD EXTENDED 25
 20    30089     1     2020-09-15    2022-09-29    PROD EXTENDED 25

[sasuser_sk]

Thank you Tom! Your approach is very valuable to me for my upcoming formulas. However, in this situation I'm identifying migration for date 28Feb2021 and 175 to 300 migration only so that I can first identify them as a flag and then exclude them from my data of better analysis.

I am expecting to flag the record that has Next start date of 28Feb2021 based on same acct no.

Like for first 2 records have same acct number. I want to flag first record because it is PROD 175 and them migrated to Prod 300.

And for obs 8&9: I want to flag 8th obs.

But again, I will be using your suggestion a lot in my work. Thank you.