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