topic Group by Month and Year and Count by Name field in SAS Programming

Group by Month and Year and Count by Name field

zdassu — Thu, 25 Oct 2018 09:23:19 GMT

Hi I was hoping someone can help, I have a data set with millions of lines and I want to group count by name field and group by date, However the date field currently has the day in there as well current format for the date field in the table is YYMMDD8, see some sample data below

Name Date

54642 18-08-31

217233 18-08-15

502368 18-08-09

541562 16-08-31

553023 16-08-09

577616 17-05-31

601365 15-06-31

Kind Regards

Zuneid

Re: Group by Month and Year and Count by Name field

Kurt_Bremser — Thu, 25 Oct 2018 09:34:25 GMT

A date always has a day, or it wouldn't be a date.

This should do it:

proc sql;
create table want as
select name, date, count(*) as count
from have
group by name, date;
quit;

or use proc summary with statistic N.

Re: Group by Month and Year and Count by Name field

FreelanceReinh — Thu, 25 Oct 2018 09:55:02 GMT

Hi @zdassu,

I think the crucial point is to apply a format to variable Date that aggregates dates to months, for example YYMMD5.

You can do this in PROC SQL as @Kurt_Bremser has suggested

proc sql;
create table want as
select name, put(date,yymmd5.) as month, count(*) as count
from have
group by name, month;
quit;

but also in PROC FREQ, PROC SUMMARY and other procedures:

proc freq data=have noprint;
format date yymmd5.;
tables name*date / out=want(drop=percent);
run;

Re: Group by Month and Year and Count by Name field

zdassu — Thu, 25 Oct 2018 10:03:48 GMT

Thanks for your help but how do I count all records in name for say Oct 2008 and so on

Re: Group by Month and Year and Count by Name field

zdassu — Thu, 25 Oct 2018 10:07:23 GMT

Thanks for your help but how do I count all records in name for say Oct 2008 and so on, so the results look like the below

Name date

2 Oct 2008

7 Nov 2008

Re: Group by Month and Year and Count by Name field

FreelanceReinh — Thu, 25 Oct 2018 10:31:26 GMT

Just use a different format, e.g. MONYY7., or combine month and year components like

select name, catx(' ',put(date,monname3.),year(date)) as month length=8, count(*) as count

in the PROC SQL solution.

Edit: Here with another format for the PROC FREQ solution:

options locale=English_UnitedStates;
proc freq data=have noprint;
format date nldateymm.;
tables name*date / out=want(drop=percent);
run;

Re: Group by Month and Year and Count by Name field

andreas_lds — Thu, 25 Oct 2018 10:52:23 GMT

@zdassu wrote:

Thanks for your help but how do I count all records in name for say Oct 2008 and so on

I haven't looked to close at the code @FreelanceReinh posted, but imho it does exactly what you want: count by name, year and month. If you want something else, please post the expected result from the input you have posted.

Re: Group by Month and Year and Count by Name field

zdassu — Thu, 25 Oct 2018 11:01:33 GMT

Thanks for your help, if I want the results to look like the below what do I have to chance in your code

month count
10-06   673
10-05   79
10-04   942
10-03   970

Re: Group by Month and Year and Count by Name field

Kurt_Bremser — Thu, 25 Oct 2018 11:12:35 GMT

@zdassu wrote:

Thanks for your help, if I want the results to look like the below what do I have to chance in your code

month count
10-06   673
10-05   79
10-04   942
10-03   970

Since you don't want name in the output, how would you modify @FreelanceReinh's SQL code?

Note that this is so easy that you will be able to do it as an "exercise for the reader".

Re: Group by Month and Year and Count by Name field

zdassu — Thu, 25 Oct 2018 11:19:50 GMT

I changed the code and it works fine

proc sql;
create table want as
select put(Date,yymmd5.) as month, count(*) as count
from have
group by month
order by month desc; 
quit;

Re: Group by Month and Year and Count by Name field

Kurt_Bremser — Thu, 25 Oct 2018 11:48:05 GMT

You can know open the knot in your handkerchief that should remind you to learn something new in SAS today 😉