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!
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
Actually sorry I need the closet translation date that happens before Event_ST and all observations greater than that
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.
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.