Proc Sql - group by formatted date value

[Julie4435637]

I am sure there is a easy way to do this but I am having a mental block now.  Here is my code:

proc sql feedback;
	select dt format=mmyys10., count(*) as cnt
	from tst
	group by dt;
quit;

Here is a sample of output.

I just want a record count by month.  I want it to be done in proc sql.  I don't want to use freq or other procedures.

[r_behata]

proc sql feedback;
	select put(dt,mmyys10.)  as dt_, 
			count(*) as cnt
	from tst
	group by  dt_;
quit;

[Reeza]

SQL doesn’t respect formats for aggregation so you need to actually convert it to a character. SAS summary PROCs don’t have that limitation. @r_behata solution illustrates how to do the conversion. 

---

@Julie4435637 wrote:

I am sure there is a easy way to do this but I am having a mental block now.  Here is my code:

proc sql feedback;
	select dt format=mmyys10., count(*) as cnt
	from tst
	group by dt;
quit;

Here is a sample of output.

I just want a record count by month.  I want it to be done in proc sql.  I don't want to use freq or other procedures.

---

[andreas_lds]

If you don't have to use proc sql, i would switch to proc summary:

proc summary data= tst nway;
  class dt;
  format dt mmyys10.;
  output out= want(drop= _type_ rename= (_freq_ = cnt));
run;

[Ksharp]

proc sql;
create table want as
select intnx('month',date,0) as date format=mmyys10.,count(*) as cnt
from sashelp.stocks
group by 1;
quit;

[Sajid01]

Hello @Julie4435637 

I see that you need "I just want a record count by month." . With this requirement, typically, one would expect  one row for each month.(As a corollary for the same months in different years, one would expect one row for each year and month combination).
In the output  you have posted there are multiple records for the same month 07/2020.
Wondering if the output  you have posted is what you want OR one row for each month (and year) ?