Counting obs by month with specific format

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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
Contributor
Posts: 65

Re: Counting obs by month with specific format

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 144

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
Contributor
Posts: 65

Re: Counting obs by month with specific format

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;

Esteemed Advisor
Esteemed Advisor
Posts: 7,223

Re: Counting obs by month with specific format

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.

Grand Advisor
Posts: 17,360

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;

Occasional Contributor
Posts: 18

Re: Counting obs by month with specific format

Thank you everyone - this all helped!

☑ This topic is SOLVED.

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

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