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!
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!
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.
Ready to level-up your skills? Choose your own adventure.