Can any one explain how can we get last date of each month for several years without using intnx function.
because intnx was introduced in 9.2 and above.
i want to know how previous coders code in this particular situation.
Soemthing like
select (month);
when (1,3,5,7,8,10,12) day = 31;
when(4,6,9,11) day = 30;
when (2) do;
if mod(year,4) ne 0 and (mod(year,400) = 0 or mod(year,100) ne 0)
then day = 29;
else day = 28;
end;
end;
Untested, posted from my tablet.
@rogeralfa111 wrote:
Can any one explain how can we get last date of each month for several years without using intnx function.
because intnx was introduced in 9.2 and above.
I assume this is an academic question.
The intnx() function existed with certainty already in versions prior to 9.2. No more sure if it was already V6 but certainly since V8.<n>
Here a link to V9.1.3 docu that includes intnx(): SAS 9.1.3 Language Reference: Dictionary, Fifth Edition
...or here a link to SAS V8 docu I found: https://www.sfu.ca/sasdoc/sashtml/lgref/z0212700.htm
Below a lazy approach that doesn't need separate logic for the different number of days per month or for dealing with leap years.
data work.last_day_of_month;
format date date9.;
do date='01jan1900'd to today();
if day(date+1)=1 then
do;
output;
date+27;
end;
end;
run;
...and of course with a given date you can just use the following test: day(date+1)=1
There are a number of books on common numeric calculations and dates is one of them.
However be a bit careful with FEBRUARY and years as some references didn't consider the year 2000, which was an exception to the common 100/400 year determination of Leap Years (hence a problem called Y2K). Be very careful of any code relying on 2-digit years because that is a symptom of problems to come.
Historically, I've also created a date dimension and queried the table.
https://gist.github.com/statgeek/9606118
EDIT: the holiday portion would need to be updated (clearly very old code)
data have;
do year=2020 to 2023;
do month=1 to 12;
date=mdy(month,1,year);
last_day=mdy(ifn(month=12,1,month+1),1,ifn(month=12,year+1,year))-date;
last_date=mdy(month,last_day,year);
output;
end;
end;
format last_date date yymmdd10.;
run;
For each year/month, get the date value for the first day of next month. Then subtract 1:
data have;
do year=2020 to 2023;
do month=1 to 12;
nxt_date1=mdy(mod(month,12)+1,1,year+(month=12)); *Next month first date *;
last_date=nxt_date1-1;
last_day=day(last_date);
output;
end;
end;
format nxt_date1 date9. last_date date9.;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.