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.
Hi @altatunc
It is a little difficult to figure out what you are trying to achieve. So my guess is:
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:
And which assigned_year do you want for start_date=2010-01-01 and end_date=2012-12-31?
2009, 2010, 2011 or 2012?
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?
Hi @altatunc
It is a little difficult to figure out what you are trying to achieve. So my guess is:
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:
Please post sample data and show the expected result so that we understand better what you are really doing.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.