BookmarkSubscribeRSS Feed
JJ19
Calcite | Level 5

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)

 

 

Capture.PNG

 

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!

3 REPLIES 3
LinusH
Tourmaline | Level 20
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).
Data never sleeps
JJ19
Calcite | Level 5

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

ChrisNZ
Tourmaline | Level 20

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1054 views
  • 0 likes
  • 3 in conversation