Hi,
I have that piece to calculate the rolling 12 months customer.
Invoice period refer to the year (2015 = 15) and month (05) that a customer traded with us.
With the following conditional statement for January,for every time a customer traded within that period (1502 -1601), it would be class as 1 or missing.
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_0001 AS
SELECT DISTINCT t1.Customer_Code,
/* MAX_of jAN16 */
(MAX(CASE
WHEN t1.Invoice_Period between 1502 and 1601
THEN 1 ELSE .
END)) LABEL="January" AS 'MAX_of jAN16'n,
/* MAX_of feb16 */
(MAX(CASE
WHEN t1.Invoice_Period between 1503 and 1602
THEN 1 ELSE .
END)) LABEL="February" AS 'MAX_of feb16'n,
/* MAX_of__MAR16 */
(MAX(CASE
WHEN t1.Invoice_Period between 1504 and 1603
THEN 1 ELSE .
END)) LABEL="March" AS MAX_of__MAR16,
/* MAX_of__APRIL16 */
(MAX(CASE
WHEN t1.Invoice_Period between 1505 and 1604
THEN 1 ELSE .
END)) LABEL="April" AS MAX_of__APRIL16,
Now I am struggling to get my head around about the possibility to a Do Loop instead of repeating the code for the rest of the year.
I think I need one Do Loop to increment the MAX_of_Month16 and another one to increment the Invoice Period on the conditional statement which will be an If statement on the data step.
Can anybody help me ?
Regards
Looks like you're using EG? You're looking for a data step solution though, via programming?
It may be better to post a sample of what you have and what you're looking for, the code looks a touch convoluted.
In general, I don't recommend naming variables with spaces, it makes it more difficult to code for one. Using a label will allow you to still have the appropriate headers.
Embedded spaces are the bane of every coder's existance.
I don't recommend against variable names with embedded spaces; I don't allow it!
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.
I am describing my logic on the following post below.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.