BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ashishj816
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
ashishj816
Quartz | Level 8
%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

7 REPLIES 7
LinusH
Tourmaline | Level 20

Renamed topic to better describe the question.

Data never sleeps
LinusH
Tourmaline | Level 20

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
ashishj816
Quartz | Level 8

But that will return me date not count

Astounding
PROC Star

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
Tourmaline | Level 20
It should return count (freq/n) by date.
Data never sleeps
ballardw
Super User

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
Quartz | Level 8
%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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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