DATA Step, Macro, Functions and more

Macro : Repeative Case statement into a Do Loop

Reply
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: 19,817

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.

 

Capture.JPG

 

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.

Capture1.JPG

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

 

Capture3.JPG

 

 

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.

 

Capture5.JPG

 

Regards

Ask a Question
Discussion stats
  • 4 replies
  • 313 views
  • 0 likes
  • 3 in conversation