topic Re: Group by Count in SAS Programming

Group by Count

BrahmanandaRao — Mon, 08 Mar 2021 05:33:09 GMT

data gcount;
set cl;
 by age;
	if first.age then counts=0;
	counts+1;
	if last.age the output;
proc print;	title 'Age Group count with Datastep';
	run;

	proc freq data=cl  ;
	 tables Age*Sex format=2.0 /out=ccc(drop=percent) nopercent norow nocum ;
	run;

	proc sql;
	select  Age,count(Age) as counts from sashelp.class	
	group by  Age;
	title 'Age Group count with proc sql';
	quit;

Here i want same output like Datastep in proc freq and proc sql

Re: Group by Count

andreas_lds — Mon, 08 Mar 2021 05:49:12 GMT

Please post data in usable form.
Explain the problem: how does the result doesn't match your expectations.
If you have a working solution, why wasting time in re-implementing it?

Re: Group by Count

PeterClemmensen — Mon, 08 Mar 2021 05:49:50 GMT

How does your results differ from your desired results?

Re: Group by Count

BrahmanandaRao — Mon, 08 Mar 2021 06:05:04 GMT

Keep name sex age height weight and age group count variables like datastep output in proc freq and proc sql methods

Re: Group by Count

PeterClemmensen — Mon, 08 Mar 2021 06:13:47 GMT

In Proc SQL;

proc sql;
   select  *, count(Age) as counts from sashelp.class	
   group by  Age;
   title 'Age Group count with proc sql';
quit;

Re: Group by Count

BrahmanandaRao — Mon, 08 Mar 2021 07:19:14 GMT

As per your code output get like below

But I want output like below

Re: Group by Count

andreas_lds — Mon, 08 Mar 2021 07:39:14 GMT

Why do you want to display the columns Name, Sex, Height and Weight?

proc summary data=sashelp.class nway;
  class Age;
  output out=work.counted(drop= _type_ rename=(_freq_=count));
run;

Re: Group by Count

Kurt_Bremser — Mon, 08 Mar 2021 08:34:40 GMT

@BrahmanandaRao wrote:

Output like that is clearly in the domain of the data step. Only a "SAS illiterate" would waste time even thinking about doing it with a different tool.

Mind that such a result depends on the way the data was sorted before the data step.

In SQL, you would need to make use of automatic remerge and a HAVING clause to filter for the "last" observation within a BY group.

With PROC FREQ, you will need an additional step that adds the columns to the FREQ output (which will only contain the TABLES variables and the statistics), and for that need to prepare data (one obs per group) with a step that is basically the same you already have.