Calculate no of, by date

[ashishj816]

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

[LinusH]

Renamed topic to better describe the question.

[LinusH]

By looking at your data (rather than reading your description), I would:

• Create new variable, that represents the 1st of the month for each observation You could use intnx() for this.
• Then use PROC FREQ that has cumulative freq as a standard output.

[ashishj816]

But that will return me date not count

[Astounding]

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;

[LinusH]

It should return count (freq/n) by date.

[ballardw]

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;

[ashishj816 · Accepted solution]

%let start_date = 01jan1990;
%let end_date = 01jul2013;
%let months = intck('month',"&start_date."d,"&end_date."d);
data dates;
do m = 0 to &months.;
first_of_month = intnx('month',"&start_date."d,m,'s');
last_of_month = intnx('month',"&start_date."d,m,'e');
lead_first_of_month = intnx('month',first_of_month,1,'s');
lead_last_of_month = intnx('month',last_of_month,1,'e');
as_of_year_month = (year(first_of_month)*100)+month(first_of_month);
output;
end;
format first_of_month last_of_month lead_first_of_month lead_last_of_month date9.;
run;


This is the solution i came with and its working for me