BookmarkSubscribeRSS Feed
Julie4435637
Obsidian | Level 7

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.

Julie4435637_0-1624405174970.png

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.

5 REPLIES 5
r_behata
Barite | Level 11
proc sql feedback;
	select put(dt,mmyys10.)  as dt_, 
			count(*) as cnt
	from tst
	group by  dt_;
quit;
Reeza
Super User

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.

Julie4435637_0-1624405174970.png

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
Jade | Level 19

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
Super User
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
Meteorite | Level 14

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1284 views
  • 5 likes
  • 6 in conversation