Group by Count

[BrahmanandaRao]

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 

[andreas_lds]

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

[PeterClemmensen]

How does your results differ from your desired results?

[BrahmanandaRao]

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

[PeterClemmensen]

In Proc SQL;

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

[BrahmanandaRao]

As per your code output get like below 

But I want output like below 

[andreas_lds]

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;

[Kurt_Bremser]

---

@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.