Counting obs by month with specific format

[myboys2]

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

[arodriguez]

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;

[MadhuKorni · Accepted solution]

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;

[RW9]

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.

[Reeza]

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;

[myboys2]

Thank you everyone - this all helped!