Solved
Contributor
Posts: 30

# Counting obs by month with specific format

Hi - I am trying to count the number of observations every month with date in format of 06/30/15

Accepted Solutions
Solution
‎07-15-2015 10:28 AM
Frequent Contributor
Posts: 79

## Re: Counting obs by month with specific format

Posted in reply to arodriguez

If the date variable is numeric then

data have1;

set have;

month = put(begin_date,monname3.);    * If you want the full month name use monname.);

year = year(begin_date);

run;

If the date variable is character then

data have1;

set have;

month = put(input(begin_date,mmddyy10.),monname3.);  * If you want the full month name use monname.);

yr =year(input(begin_date,mmddyy10.));

run;

Counting the number of obs for each month :

Using Proc Sql;

proc sql;

create table Want as

select Year,Month,count(*) as ObsCnt from have1

group by year,month;

quit;

Using Datastep;

proc sort data = have1 out =have2;

by year month;

data want;

set have2;

if first.year or first.month then ObsCnt=1;

else ObsCnt+1;

by year month;

if last.year or last.month then output;

run;

All Replies
Frequent Contributor
Posts: 146

## Re: Counting obs by month with specific format

Hi MyBoys2

You could create a variable with the month, something like

data need;

set have;

month=substrn(date,1,2);*If format is categorical;

month=substrn(put(date,mmddyy8.),1,2);*If format is numerical;

run;

Doing this you will have a variable to group, and with a sql you could count it like

proc sql;

select count (*) as number, month

from need

group by month;

quit;

Solution
‎07-15-2015 10:28 AM
Frequent Contributor
Posts: 79

## Re: Counting obs by month with specific format

Posted in reply to arodriguez

If the date variable is numeric then

data have1;

set have;

month = put(begin_date,monname3.);    * If you want the full month name use monname.);

year = year(begin_date);

run;

If the date variable is character then

data have1;

set have;

month = put(input(begin_date,mmddyy10.),monname3.);  * If you want the full month name use monname.);

yr =year(input(begin_date,mmddyy10.));

run;

Counting the number of obs for each month :

Using Proc Sql;

proc sql;

create table Want as

select Year,Month,count(*) as ObsCnt from have1

group by year,month;

quit;

Using Datastep;

proc sort data = have1 out =have2;

by year month;

data want;

set have2;

if first.year or first.month then ObsCnt=1;

else ObsCnt+1;

by year month;

if last.year or last.month then output;

run;

Super User
Posts: 9,855

## Re: Counting obs by month with specific format

Posted in reply to arodriguez

Just to note, you don't actually need to create a month variable, you can just put that in the group by:

proc sql;

select count (*) as number, month(<datevar>) as month

from need

group by month(<datavar>);

quit;

Where <datevar> should be replaced with the variable containing the date data.

Super User
Posts: 24,018

## Re: Counting obs by month with specific format

Use a format on your data with proc freq.  If you want by year month use the format yymon7. instead.

Proc freq data=have noprint;

table date_var/out=want;

format date_var monname.;

run;

Contributor
Posts: 30

## Re: Counting obs by month with specific format

Thank you everyone - this all helped!

🔒 This topic is solved and locked.

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

Discussion stats
• 5 replies
• 766 views
• 2 likes
• 5 in conversation