SyntaxHighlighter

Wednesday, October 15, 2014

Last 3 days of month using SQL

I needed to get the last 3 entries for each month of a data set. This is not as easy as the last 3 calendar days of the month so a self join was involved. This solution was supplied by Michelle at SAS technical support.

data dates ;
  do date = '01jan2013'd to '31dec2014'd ;
    var = ranuni( 123 ) * 100 ;
    if weekday( date ) not in ( 1, 7 ) then output ;
  end ;
  format date date9. ;
run ;


proc sql ;
  create table results( drop = ranking ) as
    select        *
                , ( select   count( b.date )
                      from   dates b
                      where  b.date <= a.date 
                  ) as ranking 
      from        dates a 
      group by    year( date )
                , month( date ) 
      having      ranking between max( ranking ) and max( ranking ) - 2 
      order by    date ; 
quit ;

No comments:

Post a Comment