Hi, I have written a program that is clearly commented so by looking at the code and results you should be able to clearly see what the problem is (you can directly copy-paste-run the code) :
/*
One customer can move between different segments.
There can also come new segment generations.
This program:
1. Simulates how a customer table can look at specific
timepoints (snapshots).
2. Then the expected result are printed out in a table.
3. A "from to" table is created to follow the customers
segment belonging at different periods.
4. We see that row 3, 4 and 18 from the resulting table ("actual_result")
differ from the "expected result" at the bottom of the program.
I cannot figure out why this happens?
*/
/*--------------------------------------------------------------*/
/* Creating test data. */
/* Used for creating an expected test result in the next section. */
/*--------------------------------------------------------------*/
/*The customer table at the "beginning". */
data customer_table_snapshot_1;
input date :yymmdd10. customer_id segment_generation_1 $;
format date yymmdd10.;
datalines;
2023-01-01 111 A
2023-01-01 222 B
2023-01-01 333 C
;
run;
/*Customers have changed segment but still within segment_generation_1.*/
data customer_table_snapshot_2;
input date :yymmdd10. customer_id segment_generation_1 $;
format date yymmdd10.;
datalines;
2023-01-07 111 A
2023-01-07 222 C
2023-01-07 333 A
;
run;
/*Now a new column segment_generation_2 has been added. */
data customer_table_snapshot_3;
input date :yymmdd10. customer_id segment_generation_1 $ segment_generation_2 $ ;
format date yymmdd10.;
datalines;
2023-01-10 111 B A
2023-01-10 222 C C
2023-01-10 333 A C
;
run;
/*Now both the segment_generation_1 and segment_generation_2 has changed. */
/*This is so that we can make sure the method can handle that two columns change.*/
data customer_table_snapshot_4;
input date :yymmdd10. customer_id segment_generation_1 $ segment_generation_2 $ ;
format date yymmdd10.;
datalines;
2023-01-15 111 A B
2023-01-15 222 C B
2023-01-15 333 B B
;
run;
data customer_table_snapshot_5;
input date :yymmdd10. customer_id segment_generation_1 $ segment_generation_2 $ ;
format date yymmdd10.;
datalines;
2023-02-20 111 A C
2023-02-20 222 C C
2023-02-20 333 B B
;
run;
/*--------------------------------------------------------------*/
/* Expected result from using test data. */
/*--------------------------------------------------------------*/
data expected_from_to_result;
LENGTH customer_id 8 segment_generation $ 100 segment_belonging $ 1 valid_from_date 8 valid_to_date 8;
input customer_id segment_generation $ segment_belonging $ valid_from_date :yymmdd10. valid_to_date :yymmdd10.;
format valid_from_date yymmdd10. valid_to_date yymmdd10.;
datalines;
111 segment_generation_1 A 2023-01-01 2023-01-09
111 segment_generation_1 B 2023-01-10 2023-01-14
111 segment_generation_1 A 2023-01-15 9999-12-31
111 segment_generation_2 A 2023-01-10 2023-01-14
111 segment_generation_2 B 2023-01-15 2023-02-19
111 segment_generation_2 C 2023-02-20 9999-12-31
222 segment_generation_1 B 2023-01-01 2023-01-06
222 segment_generation_1 C 2023-01-07 9999-12-31
222 segment_generation_2 C 2023-01-10 2023-01-14
222 segment_generation_2 B 2023-01-15 2023-02-19
222 segment_generation_2 C 2023-02-20 9999-12-31
333 segment_generation_1 C 2023-01-01 2023-01-06
333 segment_generation_1 A 2023-01-07 2023-01-14
333 segment_generation_1 B 2023-01-15 9999-12-31
333 segment_generation_2 C 2023-01-10 2023-01-14
333 segment_generation_2 B 2023-01-15 9999-12-31
;
run;
proc sort data=expected_from_to_result;
by customer_id segment_generation valid_from_date;
run;
/*--------------------------------------------------------------*/
/* Creating a from_to table*/
/*--------------------------------------------------------------*/
data historic_info_customers;
LENGTH customer_id 8 segment_generation $ 100 segment_belonging $ 1 valid_from_date 8 valid_to_date 8;
format valid_from_date yymmdd10. valid_to_date yymmdd10.;
stop;
run;
proc sql;
create index idx on historic_info_customers(customer_ID,valid_to_date);
quit;
%macro update_history1(snapshot);
data historic_info_customers;
set &snapshot;
valid_to_date='31dec9999'd;
modify historic_info_customers key=idx;
if not _iorc_ then do;
if segment_belonging ne segment_generation_1 then do;
valid_to_date=date-1;
replace;
valid_from_date=date;
valid_to_date='31dec9999'd;
segment_generation='segment_generation_1';
segment_belonging=segment_generation_1;
output;
end;
end;
else do;
_error_=0;
valid_from_date=date;
valid_to_date='31dec9999'd;
segment_generation='segment_generation_1';
segment_belonging=segment_generation_1;
output;
end;
run;
%mend;
%update_history1(customer_table_snapshot_1);
%update_history1(customer_table_snapshot_2);
%update_history1(customer_table_snapshot_3);
%update_history1(customer_table_snapshot_4);
%update_history1(customer_table_snapshot_5);
%macro update_history2(snapshot);
data historic_info_customers;
set &snapshot;
valid_to_date='31dec9999'd;
modify historic_info_customers key=idx;
if not _iorc_ then do;
if segment_belonging ne segment_generation_2 or segment_generation ne 'segment_generation_2' then do;
valid_to_date=date-1;
replace;
valid_from_date=date;
valid_to_date='31dec9999'd;
segment_generation='segment_generation_2';
segment_belonging=segment_generation_2;
output;
end;
end;
else do;
_error_=0;
valid_from_date=date;
valid_to_date='31dec9999'd;
segment_generation='segment_generation_2';
segment_belonging=segment_generation_2;
output;
end;
run;
%mend;
/*%update_history2(customer_table_snapshot_1); */
/*%update_history2(customer_table_snapshot_2);*/
%update_history2(customer_table_snapshot_3);
%update_history2(customer_table_snapshot_4);
%update_history2(customer_table_snapshot_5);
data actual_result;
set historic_info_customers;
run;
proc sort data=actual_result;
by customer_id segment_generation valid_from_date;
run;
/*--------------------------------------------------------------*/
/* Checking if the expected result is what we get */
/*--------------------------------------------------------------*/
/*comparing */
data expected_result;
set expected_from_to_result;
run;
data actual_result;
set actual_result;
run;
/*Row 3, 8 och 14 differ, WHY?*/
proc compare base=expected_result
compare=actual_result;
run;
Any one know why the result is not as expected?
If you see some possibilities to improve the code that is much appreciated as well.
Thanks in advance.
