DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 142

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

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_IDtransaction_date
24927929-May-14
2492791-Jun-14
24927922-Aug-14
2492791-Dec-14
24927913-Dec-14
36355529-Jun-14
3635551-Jul-14
36355521-Aug-14
36355522-Dec-14
36355513-Feb-15


This is what I want


CUSTOMER_IDtransaction_date
24927922-Aug-14
2492791-Dec-14
24927913-Dec-14
36355521-Aug-14
36355522-Dec-14
36355513-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!

Super User
Posts: 10,552

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

looks like you need

%let event_ST=21Aug14;

Where  datepart(Transaction_date) ge "&event_st"d

OR

%let event_ST='21Aug14'd;

Where  datepart(Transaction_date) ge &event_st;

after FROM a.Cust_trans

Frequent Contributor
Posts: 142

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

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

Ask a Question
Discussion stats
  • 2 replies
  • 240 views
  • 0 likes
  • 2 in conversation