BookmarkSubscribeRSS Feed
JeanTP
Fluorite | Level 6

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

 

4 REPLIES 4
Reeza
Super User

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.

 

sh0e
Obsidian | Level 7

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!

JeanTP
Fluorite | Level 6

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.

 

JeanTP
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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