Select end month dates from daily data for each company

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Select end month dates from daily data for each company

Dear All,

I would be hugely grateful, if you could please help me with the following issue:

For a particular year, I have daily series of companies,days and other variables. The dataset is sorted by companies. In other words, I have

Row     Days         Company  Variable1 Variable2 Variable3

1      27/07/2000       Firm 1

2      28/07/2000       Firm 1

3      29/07/2000       Firm 1

4      30/07/2000       Firm 1

5      31/07/2000       Firm 1

6      27/08/2000       Firm 1

7      28/08/2000       Firm 1

8      29/08/2000       Firm 1

9      30/08/2000       Firm 1

10    31/08/2000       Firm 1

Then for exactly the same days, I have firm 2. My aim is to take only observations for 31/07/2000, 31/08/2000 for firm 1, then take observations that belong to end month dates (i.e. to 31/07/2000, 31/08/2000) for firm 2, then for firm 3 and so on.

Any help would be appreciated.

Kind regards,

Ruslan


Accepted Solutions
Solution
‎07-02-2014 06:10 PM
Super User
Posts: 17,810

Re: Select end month dates from daily data for each company

There's a couple of ways to do this but this is a really easy one, possibly not very efficient though.

data want;

set have;

if days=intnx('month', days, 0, 'e') ; *Check if the date is the last day of a month then keep;

run;

View solution in original post


All Replies
Solution
‎07-02-2014 06:10 PM
Super User
Posts: 17,810

Re: Select end month dates from daily data for each company

There's a couple of ways to do this but this is a really easy one, possibly not very efficient though.

data want;

set have;

if days=intnx('month', days, 0, 'e') ; *Check if the date is the last day of a month then keep;

run;

Contributor
Posts: 71

Re: Select end month dates from daily data for each company

I have attempted huge number of combinations with intnx, none of them worked.

Your solution is so simple and effective. Huge thanks!!

Super Contributor
Posts: 644

Re: Select end month dates from daily data for each company

An even easier (and a pre intnx() golden oldie) is to detect the next day as the first day of a month

data want;

     set have;

     where Day(days + 1) = 1 ;

run ;

Richard

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 540 views
  • 1 like
  • 3 in conversation