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!!!!!!!!!!!!!!!!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: