BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
amyk
Fluorite | Level 6

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;
PG

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
amyk
Fluorite | Level 6

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. 

PGStats
Opal | Level 21

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;
PG
amyk
Fluorite | Level 6

thank you that was very helpSmiley Very Happyful!!!!!!!!!!!!!!!!!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1284 views
  • 0 likes
  • 3 in conversation