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!!!!!!!!!!!!!!!!!

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 788 views
  • 0 likes
  • 3 in conversation