Hi - I am trying to count the number of observations every month with date in format of 06/30/15
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;
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;
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;
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.
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;
Thank you everyone - this all helped!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.