Group by Month and Year and Count by Name field

[zdassu]

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

[Kurt_Bremser]

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.

[zdassu]

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

[FreelanceReinh]

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;

[FreelanceReinh]

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;

[zdassu]

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

[andreas_lds]

---

@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.

[zdassu]

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

[Kurt_Bremser]

---

@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".

[zdassu · Accepted solution]

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;

[Kurt_Bremser]

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