BookmarkSubscribeRSS Feed
Mgarret
Obsidian | Level 7

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!

2 REPLIES 2
ballardw
Super User

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

Mgarret
Obsidian | Level 7

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1252 views
  • 0 likes
  • 2 in conversation