topic Macro to Combine Municipal Proc SQL Statements Based on Date Criteria in SAS Programming

Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

RobertNYC — Thu, 11 Jun 2015 18:19:10 GMT

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!

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

PaigeMiller — Thu, 11 Jun 2015 18:30:22 GMT

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.

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

RobertNYC — Thu, 11 Jun 2015 18:56:21 GMT

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.

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

CTorres — Fri, 12 Jun 2015 19:27:11 GMT

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

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

sbb — Fri, 12 Jun 2015 21:36:55 GMT

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.

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

Tom — Fri, 12 Jun 2015 22:23:22 GMT

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);

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

Tom — Fri, 12 Jun 2015 23:02:01 GMT

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;

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;

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;

Re: Macro to Combine Municipal Proc SQL Statements Based on Date Criteria

Ron_MacroMaven — Mon, 15 Jun 2015 14:24:41 GMT

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