topic Re: Proc Sql - group by formatted date value in SAS Programming

Proc Sql - group by formatted date value

Julie4435637 — Tue, 22 Jun 2021 23:41:58 GMT

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.

Re: Proc Sql - group by formatted date value

r_behata — Wed, 23 Jun 2021 00:12:52 GMT

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

Re: Proc Sql - group by formatted date value

Reeza — Wed, 23 Jun 2021 00:37:27 GMT

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.

Re: Proc Sql - group by formatted date value

andreas_lds — Wed, 23 Jun 2021 05:57:25 GMT

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;

Re: Proc Sql - group by formatted date value

Ksharp — Wed, 23 Jun 2021 12:13:48 GMT

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;

Re: Proc Sql - group by formatted date value

Sajid01 — Wed, 23 Jun 2021 17:36:39 GMT

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) ?