topic Re: Counting obs by month with specific format in SAS Programming

Counting obs by month with specific format

myboys2 — Wed, 15 Jul 2015 13:30:31 GMT

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

Re: Counting obs by month with specific format

arodriguez — Wed, 15 Jul 2015 13:55:41 GMT

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;

Re: Counting obs by month with specific format

MadhuKorni — Wed, 15 Jul 2015 14:28:30 GMT

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;

Re: Counting obs by month with specific format

RW9 — Wed, 15 Jul 2015 14:45:50 GMT

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.

Re: Counting obs by month with specific format

Reeza — Wed, 15 Jul 2015 15:28:57 GMT

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;

Re: Counting obs by month with specific format

myboys2 — Wed, 15 Jul 2015 16:26:51 GMT

Thank you everyone - this all helped!