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
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;
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.
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
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;
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;
Thanks for your help but how do I count all records in name for say Oct 2008 and so on
@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.
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
@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".
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;
You can know open the knot in your handkerchief that should remind you to learn something new in SAS today 😉
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.