topic Re: how to get Last date of each month for severalyears in SAS Programming

how to get Last date of each month for severalyears

rogeralfa111 — Wed, 08 Nov 2023 04:26:23 GMT

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.

Re: how to get Last date of each month for severalyears

Kurt_Bremser — Wed, 08 Nov 2023 07:18:32 GMT

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.

Re: how to get Last date of each month for severalyears

Patrick — Wed, 08 Nov 2023 09:48:12 GMT

@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

Re: how to get Last date of each month for severalyears

ballardw — Wed, 08 Nov 2023 16:39:31 GMT

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.

Re: how to get Last date of each month for severalyears

Reeza — Wed, 08 Nov 2023 16:59:59 GMT

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)

Re: how to get Last date of each month for severalyears

Ksharp — Thu, 09 Nov 2023 03:09:18 GMT

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;

Re: how to get Last date of each month for severalyears

mkeintz — Thu, 09 Nov 2023 03:56:20 GMT

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;