Hi All,
I have the following data and following code:
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
30/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
30/08/2000 Firm2
31/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;
This code selects the last day of the month for each firm. How can I change it to select the one-but-last trading day of the month for each firm?
I know it should be easy question, but still I find it a bit confusing 😞
Look forward to your help.
Kind regards,
Ruslan
What is the definition of:
"one-but-last trading day of the month for each firm?"
Hi Reeza,
It is one day before the last. So if I have data that end on 31st of Jan, I need to select 30th of Jan. If the data are structured such as immediately after 31st of Jan, I have 27th of Jan, then I need to select 27th of Jan and so on. I need to do this for each company and select variables that are measured on this day.
Look forward to your help.
proc sql;
select unique
Company,
max(Days) as beforeLastDay format=ddmmyy10.
from
(select
Company,
Days
from have
group by Company
having days < max(days))
group by Company
having days = max(Days);
quit;
If you are looking for all matching records (matching on the company / day), I think the SQL approach could be modified to do that. Here's a DATA step approach instead:
proc sort data=have;
by company descending days;
run;
data want;
set have;
by company descending days;
if first.company then counter=0;
if first.days then counter + 1;
if counter = 2;
drop counter;
run;
@Astounding wrote:
If you are looking for all matching records (matching on the company / day), I think the SQL approach could be modified to do that.
Remove keyword UNIQUE to get all last-but-one-day records.
I was under the impression that the OP is interested in the last but one day per company and month. But, of course, PG's PROC SQL step can be extended accordingly, similar to the code used by the OP.
@Ruslan: Does your real dataset cover a time span greater than one year (for a particular company)? If so, your approach using the MONTH function is problematic, as it would group, e.g. August 1999 and August 2000 together and select the last day only from 2000.
Should have taken more time to read the question. Thank you @FreelanceReinh. Last but one day of the month solution:
proc sql;
select unique
Company,
max(Days) as beforeLastDay format=ddmmyy10.
from
(select
Company,
Days
from have
group by Company, intnx("MONTH", Days, 0)
having days < max(days))
group by Company, intnx("MONTH", Days, 0)
having days = max(Days);
quit;
Exactly, this without the "unique" would produce what (I thought) should be the intended result.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: