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.
proc sql feedback;
select put(dt,mmyys10.) as dt_,
count(*) as cnt
from tst
group by dt_;
quit;
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.
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;
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) ?
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.
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.