I am trying to assign a value to a database from a database that has dates and based on the dates are assigned an iteration. I want to assign the iteration to another database with the dates within that range and I keep getting one too many records. so I have original 104 records after I assign the iteration I end up with 416 records with all the iterations assigned to them.
Is there a way to assign based on the range of dates without getting a one to many. If so can you give me an example of how?
Use the BETWEEN operator in SQL:
proc sql;
create table want as
select
b.iteration,
a.*
from
have as a inner join
iterations as b on a.date between b.start_date and b.end_date;
quit;
Can you share the code you are working with, and a portion of the data?
I would like to assign the iteration based on the date range from the start date to the end date to a database that has all Dec dates.
Iteration, Start_Date, End_Dates
1, 1-Dec-18, 7-Dec-18
2, 8-Dec-18, 14-Dec-18
3, 15-Dec-18, 22-Dec-18
4, 23-Dec-18, 31-Dec-18
So when I join the tables together all the Dec dates are assigned with all the iterations.
Use the BETWEEN operator in SQL:
proc sql;
create table want as
select
b.iteration,
a.*
from
have as a inner join
iterations as b on a.date between b.start_date and b.end_date;
quit;
thank you that was very helpful!!!!!!!!!!!!!!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.