Solved
Contributor
Posts: 30

# Calculate no of, by date

[ Edited ]

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

Accepted Solutions
Solution
‎05-09-2016 02:07 AM
Contributor
Posts: 30

## Re: Calculate no of, by date

%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');
as_of_year_month = (year(first_of_month)*100)+month(first_of_month);
output;
end;
run;

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

All Replies
Super User
Posts: 5,920

## Re: Calculate no of, by date

Renamed topic to better describe the question.

Data never sleeps
Super User
Posts: 5,920

## Re: Calculate no of, by date

• 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.
Data never sleeps
Contributor
Posts: 30

## Re: Calculate no of, by date

But that will return me date not count

Super User
Posts: 6,935

## Re: Calculate no of, by date

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;

Super User
Posts: 5,920

## Re: Calculate no of, by date

It should return count (freq/n) by date.
Data never sleeps
Super User
Posts: 13,942

## Re: Calculate no of, by date

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;
``````
Solution
‎05-09-2016 02:07 AM
Contributor
Posts: 30

## Re: Calculate no of, by date

%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');
as_of_year_month = (year(first_of_month)*100)+month(first_of_month);
output;
end;