topic Assigning based on range however I am getting one to many records in SAS Programming

Assigning based on range however I am getting one to many records

amyk — Fri, 11 Jan 2019 18:31:36 GMT

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?

Re: Assigning based on range however I am getting one to many records

PaigeMiller — Fri, 11 Jan 2019 18:33:57 GMT

Can you share the code you are working with, and a portion of the data?

Re: Assigning based on range however I am getting one to many records

amyk — Fri, 11 Jan 2019 18:41:23 GMT

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.

Re: Assigning based on range however I am getting one to many records

PGStats — Fri, 11 Jan 2019 20:16:25 GMT

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;

Re: Assigning based on range however I am getting one to many records

amyk — Fri, 11 Jan 2019 21:43:58 GMT

thank you that was very helpful!!!!!!!!!!!!!!!!!