topic Re: Calculate no of, by date in SAS Enterprise Guide

Calculate no of, by date

ashishj816 — Wed, 04 May 2016 11:16:01 GMT

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

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

Re: Calculate no of, by date

LinusH — Wed, 04 May 2016 11:16:45 GMT

Renamed topic to better describe the question.

Re: Calculate no of, by date

LinusH — Wed, 04 May 2016 11:28:28 GMT

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.

Re: Calculate no of, by date

ashishj816 — Wed, 04 May 2016 12:29:53 GMT

But that will return me date not count

Re: Calculate no of, by date

Astounding — Wed, 04 May 2016 13:24:07 GMT

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;

Re: Calculate no of, by date

LinusH — Wed, 04 May 2016 13:27:54 GMT

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

Re: Calculate no of, by date

ballardw — Wed, 04 May 2016 16:43:40 GMT

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;

Re: Calculate no of, by date

ashishj816 — Mon, 09 May 2016 06:07:08 GMT

%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