BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

6 REPLIES 6
Reeza
Super User

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.

 

 

LinusH
Tourmaline | Level 20
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
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7
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
LinusH
Tourmaline | Level 20
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
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7
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

Ksharp
Super User
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;


Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 9772 views
  • 1 like
  • 4 in conversation