HELP! Automating ONLY the last Biweekly Pay Date during the month

[mcrum1]

Hello!

Our company has pay period endings that fall every two weeks on a Saturday. I’m trying to automate the last pay period date (Jan 18, Feb 15, Mar 29, etc.)  of the month into a macro for our monthly reports.  I am thinking I am supposed to do a loop, but I don’t know how to develop the logic because some months have two pay periods while others months have three pay periods. 

Here are some bi-weekly Pay Period Ending dates for January through April. I’m only needing that last Pay Period Ending Date.

Jan=  4 , 18

Feb=  1, 15

Mar= 1, 15, 29

Apr= 12,  26

If the current month is April I need to be able to pull the date “March 29”. If we are in the month of May, I need to pull the date April 26. I hope that I am explaining this okay. I’m new to this job and new to SAS.

If someone could please help me that would be greatly appreciated.  Thanks!

[Ksharp · Accepted solution]

The problem is your initial value. But it seems all your want week is odd week . therefore

%let monyy=may2014;
data _null_;

do i=intnx('month',"01&monyy"d,-1,'b') to intnx('month',"01&monyy"d,-1,'e');
 if week lt week(i,'v') and mod(week(i,'v'),2)=1 and weekday(i)=7 then do;
  week=week(i,'v');day=i;
 end;
end;
call symputx('want',put(day,date9.));
run;
%put &want;

Xia Keshan

[mcrum1]

Thanks this was really helpful!

[Haikuo]

This will give you all of the wanted Sat in one year (2014), tweaked as needed.

data have;

  do Date="04jan2014"d by 14 while(year(date)=2014);

    output;

end;

run;

proc sql;

  create table want as

    select date format=date9.  from have

   group by put(date, yymon7.)

     having date=max(date)

  order by date

;

quit;

Haikuo