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

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

1 ACCEPTED SOLUTION

Accepted Solutions
MadhuKorni
Quartz | Level 8

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

5 REPLIES 5
arodriguez
Lapis Lazuli | Level 10

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

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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
Fluorite | Level 6

Thank you everyone - this all helped!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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