How to create a "bucket" variable in SAS from ranges given in another table

Question

I am trying to create a bucket variable in SAS that will split transactions into various buckets. However, depending on the retailer where the transactions occurred, the buckets have different lengths and end points. For example, Bucket 1 for Retailer 1 is from June 2017 to July 2018, while for Retailer 2 it is from January 2018 to November 2018. The retailers, bucket labels, and end points for the buckets are stored in an Excel file which I have imported successfully. The transactions are stored in a separate table with retailer information and a "date incurred" column. I am struggling to create a bucket variable in the transactions table. Does SAS allow for conditional logic when merging, like "if the transaction date is between these two dates assign this bucket value"? Is merging even the best way to add the bucket info to the transactions table?

Thank you so much for your help - this is my first ever Stack Overflow question, and I am teaching myself SAS for the first time. Please let me know what other information I can provide to make answering this question easier!

Answer 1

Add the condition to the SQL join, for example :

transactions a
left join
buckets b on a.Retailer = b.Retailer
         and a.TransactionDate between b.BucketStart and b.BucketEnd