topic Re: Macro : Repeative Case statement into a Do Loop in SAS Programming

Macro : Repeative Case statement into a Do Loop

JeanTP — Tue, 12 Jul 2016 15:16:34 GMT

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

Re: Macro : Repeative Case statement into a Do Loop

Reeza — Tue, 12 Jul 2016 15:32:17 GMT

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.

Re: Macro : Repeative Case statement into a Do Loop

sh0e — Tue, 12 Jul 2016 16:12:05 GMT

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!

Re: Macro : Repeative Case statement into a Do Loop

JeanTP — Fri, 15 Jul 2016 07:38:57 GMT

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.

Re: Macro : Repeative Case statement into a Do Loop

JeanTP — Fri, 15 Jul 2016 07:36:30 GMT

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