BookmarkSubscribeRSS Feed
Ruslan
Calcite | Level 5

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

8 REPLIES 8
Reeza
Super User

What is the definition of:

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

Ruslan
Calcite | Level 5

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
Opal | Level 21

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;
PG
Astounding
PROC Star

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
Opal | Level 21

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

 

 

PG
FreelanceReinh
Jade | Level 19

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
Opal | Level 21

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;
PG
FreelanceReinh
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1298 views
  • 2 likes
  • 5 in conversation