Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

[RobertNYC]

Hi all,

I'm not very experienced with Macro language so any assistance will be greatly appreciated.

I have a series of proc sql statements which pull data for Active, Inactive and Lapsed customers.  I end up with 3 tables.

*Customers_Active

*Customers_InActive

*Customers_Lapsed

Active:    0-12M purchaser

Inactive: 13-24M purchaser, did not purchase 0-12M (active day range minus 12 months)

Lapsed:  25-36M purchaser, did not purchase 0-24M (inactive day range minus 24 months)

Again, In order to do this I have 3 separate proc sql statements and I start out by defining 6 macro variables. 

I want to apply macro language so  I can uses  just 1 query to produce the 3 tables.

%let actstart = '24Feb2013'd ; 

%let actend = '22Feb2014'd;

%let iactstart = '26Feb2012'd;

%let iactend= '23Feb2013'd; 

%let lapstart = '27Feb2011'd ;

%let lapend = '25Feb2012'd;

Here are the three statements

/*Active Table*/

proc sql;

create table Customers_Active as

select

household_id,

customer_id,

web_order_id,

transaction_date,

sku,

quantity,

original_price

from transaction_vw

where transaction_date >= &actstart and transaction_date <= &actend

order by customer_id; quit;

/*Inactive Table*/

Notice I am not bringing in the customer_id which is already in the Customers_Active table. This tricky when writing the macro.

proc sql;

create table Customers_Inactive as

select

household_id,

customer_id,

web_order_id,

transaction_date,

sku,

quantity,

original_price

from transaction_vw

wheretransaction_date >= & iactstart and transaction_date <= &iactend

and customer_id not in (select distinct customer_id from Customers_Active)

order by customer_id; quit;

/*Lapsed  Table*/

Notice I am not bringing in the customer_id which is not already in the Customers_Active and the Customers_Inactive table. Again, this tricky when writing the macro.

proc sql;

create table Customers_Lapsed as

select

household_id,

customer_id,

web_order_id,

transaction_date,

sku,

quantity,

original_price

from transaction_vw

wheretransaction_date >= & lapstart and transaction_date <= & lapend

and customer_id not in (select distinct customer_id from Customers_Active)

and customer_id not in (select distinct customer_id from Customers_Inactive)

order by customer_id; quit;

Again, any help is greatly appreciated. Thanks!

[PaigeMiller]

Again, In order to do this 

Do what?

You haven't even come close to mentioning the end result you want from all of this code.

[RobertNYC]

Thank you Page for your response.... 

As I mentioned, the end result is 3 tables.

*Customers_Active

*Customers_InActive

*Customers_Lapsed

• I'm pulling in the same vars for all three tables
• Just the date range changes
• In addition I don't want to include customer_id in the Customers_Inactive table which is already in the Customers_Active table
• and customer_ids  in the lapsed table which are already in the Customer_Active and Customer_inactive tables

Again, since I'm pulling in the same vars I don't want to have three separate queries to do this just one. 

[sbb]

As well as not being hard-sold on using the SAS MACRO LANGUAGE, also consider the opportunity to use the INTNX function to establish/reference your intended max-cutoff dates as you code your IF/THEN/ELSE... file-split DATA step logic.

Scott Barry

SBBWorks, Inc.

[CTorres]

Proc SQL produces ONLY one output Dataset.

You can use a SAS Data Step to generate many datasets in one pass.

The following program creates the three datasets you want using the today as the reference date to count the 0-12, 13-24 and 25-36 months back.

data Customers_Active Customers_Inactive Customers_Lapsed;

  set transaction_vw;

  if transaction_date > today()-365 then output Customers_Active;

  else if transaction_date > today()-730 then output Customers_Inactive;

  else if transaction_date > today()-1095 then output Customers_Lapsed;

run;

Use the program as a starting point to get your results.

Hope this helps,

CTorres

[Tom]

So is the question:

How to convert three separate pieces of code into a macro that take the name of the target dataset as an input so the program reduces to :

%make(Customers_Active);

%make(Customers_InActive);

%make(Customers_Lapsed);

[Tom]

You can use a data step to output multiple datasets in one pass.

If you have multiple records per CUSTOMER then you might need to use something like below to set customer level flags for Active and Inactive.

data Customers_Active Customers_Inactive Customers_Lapsed;

  keep household_id customer_id web_order_id transaction_date sku quantity original_price ;

  do until (last.customer) ;

    set transaction_vw;

    by customer_id;

    if transaction_date >= &actstart and transaction_date <= &actend then do;

       Customers_Active=1;

         output Customers_Active;

      end;

  end;

  do until (last.customer) ;

    set transaction_vw;

    by customer_id;

    if transaction_date >= &iactstart and transaction_date <= &iactend

       and NOT Cusotmers_Active then do;

       Customers_Inactive=1;

         output Customers_Inactive;

      end;

  end;

  do until (last.customer) ;

    set transaction_vw;

    by customer_id;

    if (transaction_date >= &lapstart and transaction_date <= &lapend)

and NOT Customers_Active and NOT Customers_Inactive then

        output Customers_Lapsed

    ;

  end;

run;

[Ron_MacroMaven]

This paper has statements which use the interval-indexing functions

to calculate start and end dates from one macro variable with a date.

http://www.sascommunity.org/wiki/Macro_Loops_with_Dates