Select last-but-one day of the month

[Ruslan]

Hi All,

I have the following data and following code:

data have;

informat days ddmmyy10. company $8. ;format days date9.;

input  Days         Company ;

cards;

27/07/2000       Firm1

28/07/2000       Firm1

29/07/2000       Firm1

30/07/2000       Firm1

31/07/2000       Firm1

27/08/2000       Firm1

28/08/2000       Firm1

29/08/2000       Firm1

30/08/2000       Firm1

30/08/2000       Firm1

30/08/2000       Firm1

27/07/2000       Firm2

28/07/2000       Firm2

29/07/2000       Firm2

30/07/2000       Firm2

31/07/2000       Firm2

27/08/2000       Firm2

28/08/2000       Firm2

29/08/2000       Firm2

30/08/2000       Firm2

30/08/2000       Firm2

31/08/2000       Firm2

31/08/2000       Firm2

;

run;

proc sql;

  create table want as

  select company, days, month(days) as month

  from have

  group by company, calculated month

  having days=max(days);

quit;

This code selects the last day of the month for each firm. How can I change it to select the one-but-last trading day of the month for each firm?

I know it should be easy question, but still I find it a bit confusing 😞

Look forward to your help.

Kind regards,

Ruslan

[Reeza]

What is the definition of:

"one-but-last trading day of the month for each firm?"

[Ruslan]

Hi Reeza,

It is one day before the last. So if I have data that end on 31st of Jan, I need to select 30th of Jan. If the data are structured such as immediately after 31st of Jan, I have 27th of Jan, then I need to select 27th of Jan and so on. I need to do this for each company and select variables that are measured on this day.

Look forward to your help.

[PGStats]

proc sql;
select unique
    Company,
    max(Days) as beforeLastDay format=ddmmyy10.
from
    (select 
        Company,
        Days
     from have
     group by Company
     having days < max(days))
group by Company
having days = max(Days);
quit;

[Astounding]

If you are looking for all matching records (matching on the company / day), I think the SQL approach could be modified to do that.  Here's a DATA step approach instead:

proc sort data=have;

by company descending days;

run;

data want;

set have;

by company descending days;

if first.company then counter=0;

if first.days then counter + 1;

if counter = 2;

drop counter;

run;

[PGStats]

---

@Astounding wrote:

If you are looking for all matching records (matching on the company / day), I think the SQL approach could be modified to do that. 

Remove keyword UNIQUE to get all last-but-one-day records.

[FreelanceReinh]

I was under the impression that the OP is interested in the last but one day per company and month. But, of course, PG's PROC SQL step can be extended accordingly, similar to the code used by the OP.

@Ruslan: Does your real dataset cover a time span greater than one year (for a particular company)? If so, your approach using the MONTH function is problematic, as it would group, e.g. August 1999 and August 2000 together and select the last day only from 2000.

[PGStats]

Should have taken more time to read the question. Thank you @FreelanceReinh. Last but one day of the month solution:

proc sql;
select unique
    Company,
    max(Days) as beforeLastDay format=ddmmyy10.
from
    (select 
        Company,
        Days
     from have
     group by Company, intnx("MONTH", Days, 0)
     having days < max(days))
group by Company, intnx("MONTH", Days, 0)
having days = max(Days);
quit;

[FreelanceReinh]

Exactly, this without the "unique" would produce what (I thought) should be the intended result.