I want to calculate Number of joins on 1st of every month and on 30th of every month
If I have Join as Column in which data is like this
Join
01Jan2003
05Feb2003
17Feb2003
02Mar2003
04Mar2003
17Mar2003
29Mar2003
05Jan2004
09Jan2004
12Jan2004
03Feb2004
15Feb2004
15Feb2004
16Feb2004
I want output as
Month_Year Count
1Jan2003 1
1FEB2003 1
1Mar2003 3
1Apr2003 7
1May2003 7
1Jun2003 7
1Jul2003 7
1Aug2003 7
1Sep2003 7
1Oct2003 7
1Nov2003 7
1Dec2003 7
1Jan2004 7
1Feb2004 10
1Mar2004 14
Renamed topic to better describe the question.
By looking at your data (rather than reading your description), I would:
But that will return me date not count
It's not clear from your explanation whether JOIN is a character string or a SAS date. So I'll assume it's a SAS date and you will have to convert it if that's not the case.
First, create a set of your cutoff points:
data cutoffs;
do i=1 to 15;
join = intnx('month', '01jan2003'd, i);
output;
end;
drop i;
run;
Then combine that with your actual data to calculate and print counts;
data _null_;
file print;
if _n_=1 then put 'Month_Year' @20 'Count';
set have (in=another_join) cutoffs;
by join;
if another_join then count + 1;
else put join date9. @20 count 4.;
run;
See if this gives you what you want. Nothing special needed just the right format:
data have;
input Join date9.;
datalines;
01Jan2003
05Feb2003
17Feb2003
02Mar2003
04Mar2003
17Mar2003
29Mar2003
05Jan2004
09Jan2004
12Jan2004
03Feb2004
15Feb2004
15Feb2004
16Feb2004
;
run;
proc freq data=have;
tables join;
format join MonYY7.;
run;
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 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.