DATA Step, Macro, Functions and more

Subset Counts

Occasional Contributor
Posts: 14

Subset Counts

I am working with a transaction type data set where there are multiple observations for individual children nested within families.  Each child has a unique" child_id" and an associated "familynumber." I would like to generate a count of the number of children in each family.  There are 746,000 observations from 76,000 families.  I have attempted to use the following code, but each of the 76,000 subsets (one for each family) takes about 4 seconds to process.  Does anyone know of a more efficient way to go about generating this count variable?  Thank you for your help.

proc sql noprint;

select left(put(count(distinct(familynumber)),15.0)) into :fmcount work.time;

select distinct(familynumber) into :family1 - :family&fmcount from work.time;


%macro family_count;

%do i = 1 %to &fmcount;

proc sql noprint;

select count(distinct(child_id)) from work.time

where familynumber = &&family&i;



%mend family_count;


Respected Advisor
Posts: 4,654

Re: Subset Counts

As far as I can see, you don't need macro programming at all to do this.

proc sql;

create table familyCounts as

select familyNumber, count(distinct child_id) as childCount

from time

group by familyNumber;



Super User
Posts: 17,868

Re: Subset Counts

My fav is the double proc freq, one because it will give you a list of family/child list, and the second output will be the number of children per family.

proc freq data=time noprint;

table familynumber*child_id/out=family_child_list;


proc freq data=family_child_list no print;

table familynumber/out=family_child_count;


Super User
Posts: 9,682

Re: Subset Counts

No need for double proc freq , just add an option NLEVELS . if I am not making a mistake .

data class;set sashelp.class;run;
proc sort data=class;by sex;run;
ods select NLevels;
ods output NLevels=want;
proc freq data=class nlevels;
by sex;
tables age;

Xia Keshan

Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation