New Contributor
Posts: 3

# Macro : Repeative Case statement into a Do Loop

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

Super User
Posts: 23,727

## Re: Macro : Repeative Case statement into a Do Loop

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.

Contributor
Posts: 25

## Re: Macro : Repeative Case statement into a Do Loop

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!

New Contributor
Posts: 3

## Re: Macro : Repeative Case statement into a Do Loop

[ Edited ]

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.

New Contributor
Posts: 3

## Re: Macro : Repeative Case statement into a Do Loop

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

Discussion stats
• 4 replies
• 336 views
• 0 likes
• 3 in conversation