Hi, Reeza Yes I am using EG and yes i am looking for a data step solution through programming or any solution shoe On EG after applying the Max(Function) on a variable, the variable is by default embedded with spaces. But i do rename it for reporting purposes. The Rolling 12m Last Year table which is the table described on the precedent post have duplicate customer codes values by invoice period because it is a transactional data. With the following conditional statement for January,for every time a customer traded within that period (1502 -1601), it would class as 1. (CASE WHEN t1.Invoice_Period between 1502 and 1601 THEN 1 ELSE . END However, it can result that if a customer traded 5 times within that period,the customer would appear 5 times with a value of 1. So when i do the count on the next query, this customer would appear five times as the Count function only count non missing value. In the next query, I negate that effect by performing the Max function for each period. Because we know that value can be either 1 or blank, the function will give me the highest value per customer, removing duplicate customer values as you can see below for customer 000003. If the highest value is blank the customer did not trade within the pre-defined period PROC SQL;
CREATE TABLE WORK.QUERY_FOR_TP_ONLY_3YEARS_BR_0006 AS
SELECT /* January */
(COUNT(t1.'MAX_of jAN16'n)) LABEL="January" AS January,
/* February */
(COUNT(t1.'MAX_of feb16'n)) LABEL="February" AS February,
/* March */
(COUNT(t1.MAX_of__MAR16)) LABEL="March" AS March,
/* April */
(COUNT(t1.MAX_of__APRIL16)) LABEL="April" AS April,
/* May */
(COUNT(t1.MAX_of__MAY16)) LABEL="May" AS May,
/* June */
(COUNT(t1.MAX_of__JUNE16)) LABEL="June" AS June
FROM WORK.QUERY_FOR_TP_ONLY_3YEARS_BR_0001 t1;
QUIT; Then I can do the count on the next query to find out our rolling customers. Regards
... View more