topic Re: Select end month dates from daily data for each company in SAS Programming

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

Ruslan — Fri, 04 Jul 2014 13:31:58 GMT

Guys,

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.

THE QUESTION MENTIONED ABOVE IS ANSWERED, COULD YOU PLEASE ANSWER THE QUESTION BELOW:

For some months (except Feb), the end is not on 31st or 30th, but on 29th or 28th. For instance, for Jan 1998 in my sample, the end is on 30th of January and using your codes, I cannot extract 30th of January. SAS just skips January and moves to February or March (to months where the end is 30th or 31st). Any ideas how I can solve this problem?

I would hugely appreciate any help. Thank a lot for all replies regarding the first question. I found all your codes extremely useful.

Kind regards,

Ruslan

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

RW9 — Fri, 04 Jul 2014 13:51:19 GMT

Maybe have a look at intnx: http://www.datimpact.be/tips/finding-the-last-day-of-the-month-in-sas-using-intnx.php

Or: https://philihp.com/blog/2011/how-to-get-the-last-day-of-a-month-in-sas/

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

Ruslan — Fri, 04 Jul 2014 14:11:47 GMT

I have tried this, but it still skips January and goes to March. Could you please correct the code or suggest an alternative? Thanks in advance!!!

data want; /* take end of month for each company */

set have;

if date~=(intnx('month', date, 1)-1) then delete;

run;

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

RW9 — Fri, 04 Jul 2014 14:22:37 GMT

Try the other example given:

data want;

set have;

if date ne intnx(‘month’,date,0,’e’) then delete;

run;

This will give you the last day in the month

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

RW9 — Fri, 04 Jul 2014 14:25:49 GMT

This works for me:

data have;

attrib date format=date9.;

date='05JAN2014'd;

output;

date='31JAN2014'd;

output;

run;

data want;

set have;

if date ne intnx('month',date,0,'e') then delete;

run;

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

Ruslan — Fri, 04 Jul 2014 14:27:58 GMT

I have used that code originally and the results are the same. Reeza has suggested this code.

Then, RichardinOz has suggested another way of extracting the last day of month:

data want;

set have;

where Day(days + 1) = 1 ;

run ;

Both ways give exactly the same results. SAS skips January where the end in my sample is 30th and goes to March 31st. It even skips February because the end day of Feb in my sample is 26th. Could you please propose a solution?

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

Ruslan — Fri, 04 Jul 2014 14:32:21 GMT

Try to do:

data have;

attrib date format=date9.;

date='05JAN2014'd;

output;

date='30JAN2014'd;

output;

run;

and it will not work!!! But I need to get 30 Jan

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

Peter_C — Fri, 04 Jul 2014 15:21:17 GMT

Is this a problem of interpretation as much as of sas code!

Are we to create a table for each company with the lastest date in each month?

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

Ruslan — Fri, 04 Jul 2014 15:28:39 GMT

Yes, you are exactly right. I need to create a table for each company with the latest date in each month.

The problem is that these days are trading days and thus date series is with gaps. So the last day in a month could be 27th or 26th as well as the start date in a month could be 2nd,4th. I do not know how to adjust intnx function to be able to cope with gaps.

Could you please help me with this issue?

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

Peter_C — Fri, 04 Jul 2014 15:33:33 GMT

Assuming data sorted by company, days

data v/view=v;

Set have;

Month=intnx('month', days,0, 'e');

Run;

Data want;

Set v;

By company month days;

If last.month ;

Run;

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

Ruslan — Fri, 04 Jul 2014 16:14:42 GMT

Thanks a lot, Peter.C, for your code. That worked perfectly for selecting end month dates

However, it seems like this code has taken only one observation for end month dates, but I have more than one observation for Jan 30. How I can take all of them? (not just one observation per end month date)

Look forward to hearing from you soon.

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

OS2Rules — Fri, 04 Jul 2014 16:42:30 GMT

I think the problem is that you don't want the month-end date, you want the last date in each month that has data in it.

Firstly find the month and year of each record then select the highest value of date within month / year. This gives you the

last data date of each month.

Then merge this back to your data and keep those records that have matching dates.

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

Ruslan — Fri, 04 Jul 2014 16:52:44 GMT

Yes, I have been thinking about merging as well.

But I have big datasets (approx. 4GB) and I think merging will take too much time (either using proc sql or merge). Thus, I've been planning to find the last date in each month that data in it (you are absolutely right!!!) just on-one-go, at once. Is there a direct way to do this avoiding merging?

Look forward to your answers.

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

OS2Rules — Fri, 04 Jul 2014 17:35:11 GMT

I think that is the best I can do. I was thinking of sorting by day/month/hear then taking the last.day, but that will only

give you 1 record.

I think the merge is the way to go.

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

Reeza — Fri, 04 Jul 2014 18:06:24 GMT

This is a two pass solution, but in one step. A 4GB file will process fine unless you need it back in 2 secs.

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;

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

Ruslan — Fri, 04 Jul 2014 19:01:16 GMT

Thanks a lot, Reeza! That works perfectly.

Just quick question/clarification:

I have other variables (about 12). Can I include these variables by typing "select var1 var2 var3 company, days, month(days) as month..."? or is there any other way?

Huge thanks again. Look forward to hearing from you soon.

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

Reeza — Fri, 04 Jul 2014 19:54:17 GMT

Try it and let me know

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

Ruslan — Fri, 04 Jul 2014 20:05:39 GMT

That works perfectly as well. Many thanks, Reeza, for your help.

One minor worry:

When I run proc sql, at the end I got this message: Note: The query requires remerging summary statistics back with the original data. Can this note cause any problems whatsoever? Any missing data?

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

Reeza — Fri, 04 Jul 2014 20:41:28 GMT

No, this is telling you that it is a two pass solution and merging but it is doing it for you automatically.

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

Ruslan — Fri, 04 Jul 2014 20:43:46 GMT

That's great! I hugely appreciate your help

Kind regards.

Ruslan