## Count child by parent

Solved
Occasional Contributor
Posts: 15

# Count child by parent

Hi,

I have this data set: 1 parent with 2 children, both children appears on each case. case nr 2 repeat itself three tims for child nr 465. I need to create a variable (counter) which counts the children by the parent-ID. The expected results are dispayed on the right column "wanted counter". Can anyone sujest how to solve it? thanks.

obsCASEID-parentID-childWanted counter

1

1101111
21104552
32101111
4210

455

2
52104552
62104552

Accepted Solutions
Solution
‎09-04-2012 10:43 AM
Posts: 3,167

## Re: Count child by parent

Give the data as is, and with the assumption that the data is sorted or at least clustered:

data have;

input CASE    ID_parent    ID_child ;

cards;

1    10    111

1    10    455

2    10    111

2    10    455

2    10    455

2    10    455

;

data want;

set have;

by case id_parent Id_child notsorted;

if first.case then call missing (count);

count+first.id_child;

run;

proc print;run;

Haikuo

All Replies
Solution
‎09-04-2012 10:43 AM
Posts: 3,167

## Re: Count child by parent

Give the data as is, and with the assumption that the data is sorted or at least clustered:

data have;

input CASE    ID_parent    ID_child ;

cards;

1    10    111

1    10    455

2    10    111

2    10    455

2    10    455

2    10    455

;

data want;

set have;

by case id_parent Id_child notsorted;

if first.case then call missing (count);

count+first.id_child;

run;

proc print;run;

Haikuo

Occasional Contributor
Posts: 15

## Re: Count child by parent

Thanks! I will try it!

Super Contributor
Posts: 474

## Re: Count child by parent

Using lag, and deriving from Mike's simplified code here: https://communities.sas.com/message/137090#137090

data WANT;

set HAVE;

retain WANTED;

WANTED=sum(WANTED*(lag(CASE) = CASE),(lag(ID_child) ^= ID_child));

run;

More on the LAG function here:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/a000212547.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

🔒 This topic is solved and locked.