Count child by parent

[MCB]

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.

obs	CASE	ID-parent	ID-child	Wanted counter
1	1	10	111	1
2	1	10	455	2
3	2	10	111	1
4	2	10	455	2
5	2	10	455	2
6	2	10	455	2

[Haikuo · Accepted solution]

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

[MCB]

Thanks! I will try it!

[DanielSantos]

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