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
I am writing program code within SAS EG.
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 |
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
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.