DATA Step, Macro, Functions and more

sas sql last business day of the month

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 79
Accepted Solution

sas sql last business day of the month

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!


Accepted Solutions
Solution
‎08-05-2016 06:03 PM
Super User
Posts: 9,681

Re: sas sql last business day of the month

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;


View solution in original post


All Replies
Super User
Posts: 17,840

Re: sas sql last business day of the month

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.

 

 

Super User
Posts: 5,257

Re: sas sql last business day of the month

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.
Data never sleeps
Frequent Contributor
Frequent Contributor
Posts: 79

Re: sas sql last business day of the month

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
Super User
Posts: 5,257

Re: sas sql last business day of the month

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?
Data never sleeps
Frequent Contributor
Frequent Contributor
Posts: 79

Re: sas sql last business day of the month

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

Solution
‎08-05-2016 06:03 PM
Super User
Posts: 9,681

Re: sas sql last business day of the month

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;


☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 410 views
  • 0 likes
  • 4 in conversation