Proc SQL: Select Observations which are GE to a last transaction date before a Start Date

Mgarret — Mon, 29 Jun 2015 19:58:12 GMT

Hi,

I want to bring back all the observations which are greater than or equal to the last transaction date before event_ST or in other words all the observations which are greater than or equal to the Maximum transaction_date that is less than event_ST

This is what I have:

CUSTOMER_ID	transaction_date
249279	29-May-14
249279	1-Jun-14
249279	22-Aug-14
249279	1-Dec-14
249279	13-Dec-14
363555	29-Jun-14
363555	1-Jul-14
363555	21-Aug-14
363555	22-Dec-14
363555	13-Feb-15

This is what I want

CUSTOMER_ID	transaction_date
249279	22-Aug-14
249279	1-Dec-14
249279	13-Dec-14
363555	21-Aug-14
363555	22-Dec-14
363555	13-Feb-15

I have been experimenting with syntax combos and I can’t seem to get it right

(max(transaction_date) < &event_ST

Here is my code so far:

%let event_ST='21Aug14'd;

proc sql;

create table a.have as

select distinct

customer_id,

web_order_id,

datepart(transaction_date) as transaction_date format=date9. ,

max(quantity) as quantity ,

sum(amount) as amount format dollar10.2

from a.Cust_trans

group by customer_id, TRANSACTION_DATE

order by customer_id, TRANSACTION_DATE;

quit;

Any help will be greatly appreciated!

Re: Proc SQL: Select Observations which are GE to a last transaction date before a Start Date

ballardw — Mon, 29 Jun 2015 20:17:39 GMT

looks like you need

%let event_ST=21Aug14;

Where datepart(Transaction_date) ge "&event_st"d

%let event_ST='21Aug14'd;

Where datepart(Transaction_date) ge &event_st;

after FROM a.Cust_trans

Re: Proc SQL: Select Observations which are GE to a last transaction date before a Start Date

Mgarret — Mon, 29 Jun 2015 20:35:57 GMT

Actually sorry I need the closet translation date that happens before Event_ST and all observations greater than that

topic Re: Proc SQL: Select Observations which are GE to a last transaction date before a Start Date in SAS Programming

Proc SQL: Select Observations which are GE to a last transaction date before a Start Date

Re: Proc SQL: Select Observations which are GE to a last transaction date before a Start Date

Re: Proc SQL: Select Observations which are GE to a last transaction date before a Start Date