BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
altatunc
Obsidian | Level 7

I have a data set with start and stop dates and i need to assign it a year based off some simple logic.  Given the years span unknown number of years, I'd like to embed this into a do loop or macro or both. Also just trying to understand loops and macros a bit more here.

 

Here is what the logic would look like for two years:

 

If Start_Date <= "01OCT010"d and End_Date > "01OCT2010"d and Start_Date ne "31DEC9999"d
             then Assigned_Year = 2010;
If Start_Date <= "01OCT011"d and End_Date > "01OCT2011"d and Start_Date ne "31DEC9999"d
            then Assigned_Year = 2011; 

 

In short, what is the best way to embed this into a loop/macro to assign this?  I can assign it a starting year or date but still struggling with the syntax just a bit.

Thanks in advance.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @altatunc 

 

It is a little difficult to figure out what you are trying to achieve. So my guess is:

  • You have some data with observations containing a time interval Start_Date-End_Date spanning any period.
  • You want to test the time interval against a consecutive list of years (one for each year in your code). 
  • for each observation, you want an output record for each year where October1. in the given year is present in the time interval.

If my guess is correct, then this code will work:

data have;
  format Start_Date End_Date date9.;
  ID = 1; Start_Date = '03apr2004'd; End_Date = '31dec2012'd; output;
  ID = 2; Start_Date = '01oct2018'd; End_Date = '31aug2020'd; output;
run;

data want;
  set have;
  do assigned_year = 2008 to 2021;
   if Start_Date <= mdy(10,1,assigned_year) <= End_Date then output;
  end;
run;

Result:

years.gif

View solution in original post

5 REPLIES 5
ballardw
Super User

First hint: Use of 2-digit years is a BAD IDEA. Google costs associated with Y2K. There is absolutely no reason to use 2-digit years in date literals "01OCT10"d really should be "01OCT2010"D

 

Second hint: If the use of '31DEC9999'd is for anything resembling invalid, not present, not recorded or such meaning then you may actually want to use a MISSING value in SAS instead.

 

Just how many of these conditional values are to be assigned?

 

 

 

 

 

 

 

ErikLund_Jensen
Rhodochrosite | Level 12

Hi @altatunc 

 

It is a little difficult to figure out what you are trying to achieve. So my guess is:

  • You have some data with observations containing a time interval Start_Date-End_Date spanning any period.
  • You want to test the time interval against a consecutive list of years (one for each year in your code). 
  • for each observation, you want an output record for each year where October1. in the given year is present in the time interval.

If my guess is correct, then this code will work:

data have;
  format Start_Date End_Date date9.;
  ID = 1; Start_Date = '03apr2004'd; End_Date = '31dec2012'd; output;
  ID = 2; Start_Date = '01oct2018'd; End_Date = '31aug2020'd; output;
run;

data want;
  set have;
  do assigned_year = 2008 to 2021;
   if Start_Date <= mdy(10,1,assigned_year) <= End_Date then output;
  end;
run;

Result:

years.gif

altatunc
Obsidian | Level 7
Thanks so much. This is exactly what i was looking for and far simpler than i thought at that!
Thanks again,
AT
andreas_lds
Jade | Level 19

Please post sample data and show the expected result so that we understand better what you are really doing.

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
  • 5 replies
  • 1236 views
  • 2 likes
  • 5 in conversation