topic Re: sas sql last business day of the month in SAS Programming

sas sql last business day of the month

hdg — Fri, 15 Jul 2016 22:17:24 GMT

Hi,
i have a fairly simple question. I would like to use

proc sql in SAS to extract the last business day from a set of records.

Not sure how I can do that.

Any help is appreciated

Thank you!

Re: sas sql last business day of the month

Reeza — Fri, 15 Jul 2016 23:02:11 GMT

Business day is a hard definition and not built into SAS because of various holidays around the world.

You can find the last weekday using the INTNX function, but its usually relative to some other day so I'm not sure what you're referencing. Use the WEEKDAY as the interval in INTNX along with the respective alignment to get the last one desired.

Re: sas sql last business day of the month

LinusH — Sat, 16 Jul 2016 08:43:07 GMT

I think from a set of records I would rather go with a filter approach:

Select max(business_day) as max day
From have
Where weekday(business_day) not in(1,7);

You could further develop this by adding a lookup table with site specific holidays which could use in the filter as well.

Re: sas sql last business day of the month

hdg — Sat, 16 Jul 2016 15:24:13 GMT

Hi thanks so much for your response but I would like to only pull a smaller number of records since pulling all records takes more time that's why I need to use sql , I guess th better logic really is to use sql in SAS to pull records on the last available date in any given month

Re: sas sql last business day of the month

LinusH — Sat, 16 Jul 2016 21:05:19 GMT

What do you mean by "pull"?
You need to scan all records to find the highest date, or am I missing something in your logic?

Re: sas sql last business day of the month

hdg — Sun, 17 Jul 2016 00:44:28 GMT

Hello
Sorry for not being clear. Thanks again for your help. I am trying to get a subset of records from a table in oracle I use proc sql
In SAS and connection from odbc. The table has an entry every week day I would like to get the last entry in any given month

Re: sas sql last business day of the month

Ksharp — Sun, 17 Jul 2016 02:08:06 GMT

So you want the last working day of each month ?


data have;
input date : date9.;
format date date9.;
cards;
29jan2016
29apr2016
31may2016
30jun2016
30jul2016
;
run;
proc sql;
select *
 from have
  where date=intnx('weekday',intnx('month',date,0,'e'),0);
quit;