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.
Click image to register for webinar
