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;
quit;
%macro family_count;
%do i = 1 %to &fmcount;
proc sql noprint;
select count(distinct(child_id)) from work.time
where familynumber = &&family&i;
quit;
%end;
%mend family_count;
%family_count;
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;
quit;
PG
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;
run;
proc freq data=family_child_list no print;
table familynumber/out=family_child_count;
run;
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; run;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.