topic Re: Select one-but-last day of the month in SAS Programming

Select last-but-one day of the month

Ruslan — Thu, 09 Jun 2016 18:36:48 GMT

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

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

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

Re: Select one-but-last day of the month

Reeza — Thu, 09 Jun 2016 18:18:29 GMT

What is the definition of:

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

Re: Select one-but-last day of the month

Ruslan — Thu, 09 Jun 2016 18:24:02 GMT

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.

Re: Select last-but-one day of the month

PGStats — Thu, 09 Jun 2016 18:37:37 GMT


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;

Re: Select last-but-one day of the month

Astounding — Thu, 09 Jun 2016 18:56:20 GMT

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;

Re: Select last-but-one day of the month

PGStats — Thu, 09 Jun 2016 19:09:22 GMT

@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.

Re: Select last-but-one day of the month

FreelanceReinh — Thu, 09 Jun 2016 20:35:16 GMT

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.

Re: Select last-but-one day of the month

PGStats — Thu, 09 Jun 2016 21:00:28 GMT

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;

Re: Select last-but-one day of the month

FreelanceReinh — Thu, 09 Jun 2016 21:06:03 GMT

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