topic Re: request for calculation no. of records in each sub group in SAS Procedures

request for calculation no. of records in each sub group

ssas — Fri, 05 Jun 2009 08:39:46 GMT

Hi All,
I have a file like
name postcode branch
simon me1 4en ctm
john me1 3ed rnt
tst3 me1 7fg str
hfjk me2 4kw ctm
jdk me2 6df str
jde me2 8dj rnt
.
.
.
.
;

i would like to find the no. of records for
pcode branch norecs
me1 ctm 25
me1 str 15
me2 rnt 46
....

i have used the following code

data test2(rename=(pcode1=pcode));
set test1;
* pcode is postcode;
pcode1=substr(pcode,1,3);
den=length(pcode1);
if length(pcode1)<3 then delete;
drop pcode;
run;

proc sort data=test3 nodup out=test4;
by pcode lastname branch;
run;

data test6;
set test4;
by pcode;
retain npc;
if first.pcode then do;
npc=0;
** i think some code here;
end;
npc+1;
if last.pcode then output;
run;

Now i am getting the how many records me1,me2.... but i would like to get
me1 ctm ??
me1 str ??
me1 rnt ??
me2 ctm ?
.....

Thanks in advance
Sams

Re: request for calculation no. of records in each sub group

deleted_user — Fri, 05 Jun 2009 12:07:34 GMT

proc sql;
create table l as select *, count(postcode) as cnt from l2 where postcode='me1' and branch='ctm' ;
run;

do the same for the remaining conditions then u will get 3 datasets then keep set for the three datasets.

Re: request for calculation no. of records in each sub group

deleted_user — Fri, 05 Jun 2009 17:31:03 GMT

Try the following code. It is simple with Proc SQL instead od datastep;

proc sql;
create table test1 as
select postcode, branch, count(*) as norecs
from test
group by postcode, branch
order by postcode, branch;
quit;

~ Sukanya E

Re: request for calculation no. of records in each sub group

Cynthia_sas — Fri, 05 Jun 2009 20:56:20 GMT

Hi:
While many good SQL solutions have been posted, do not overlook PROC FREQ or PROC TABULATE. They will both create counts and/or percents for you.

cynthia

[pre]

proc freq data=postdata;
tables pcode1*branch / list;
run;

proc tabulate data=postdata;
class pcode1 branch;
table pcode1 all,
branch*n all*n;
keylabel n=' ';
run;

[/pre]

Thanks a lot

ssas — Mon, 08 Jun 2009 10:36:49 GMT

Excellent,
Thanks a lot for your help.
Sukanya's and Cynth's suggestions are really helpful.
Many thanks mate(s)