Counting unique number of all past occurences by groups

[JJ19]

Dear community,

I've searched forum and was trying to solve it on my own for few days now. I tried proc sql and data step and nothing works.

My data consists of:

firm_ID

event_date for each firm in my sample

Alliance_ID: alliance that this firm is part of

Partner_ID: partners in the alliance

Alliance_Date_Announced and DateAllianceEnd: are the start and end dates of the alliances.

I need to compute for each firm_ID and each partner_ID, number of all unique alliances that a firm had prior to each event date. In other words, prior alliance history (tenure) for each firm but prior to event_date.

I also need to compute for each firm_ID, number of past alliances prior to event_date.

The problem I am facing that I am double counting ongoing alliances at the time of the event. I am interested in the history of alliances: the ones that firm was part of in the past and currently are not active any more.

Please see screenshot below (also excel file is attached)

My SAS code:

data try8 ; set stat6;
	by Focal_Firm_ID Partner_ID  Alliance_ID    ;
	if not missing(Alliance_ID) then do;
		if first.Partner_ID then do ;
				seq+1;
		end;
	end;
run;

Any help is much appreciated!

[LinusH]

This looks like a graph problem to me. If you have licence for OR or Optimization you could try one the PROCs like OPTGRAPH, OPTNETWORK or OPTNET (not particularly familiar with those, this is more of a gut feeling suggestion).

[JJ19]

Hello LinusH, you gave me some useful hints. Really appreciate your help! Never heard about OPTNET so I will check it out.

[ChrisNZ]

I can think of a sleek way to try tackling your question.

But since I will not download an Excel file or type in data, I won't try.

You really should put more effort in your post.