Desktop productivity for business analysts and programmers

Calculate no of, by date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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
Occasional Contributor
Posts: 14

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');
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

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,202

Re: Calculate no of, by date

Renamed topic to better describe the question.

Data never sleeps
Esteemed Advisor
Posts: 5,202

Re: Calculate no of, by date

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.
Data never sleeps
Occasional Contributor
Posts: 14

Re: Calculate no of, by date

But that will return me date not count

Esteemed Advisor
Posts: 5,013

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;

Esteemed Advisor
Posts: 5,202

Re: Calculate no of, by date

It should return count (freq/n) by date.
Data never sleeps
Grand Advisor
Posts: 10,262

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
Occasional Contributor
Posts: 14

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');
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
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 435 views
  • 0 likes
  • 4 in conversation