Counting the number of specific rows

[jimmychoi]

Hi all,

this is how my data would look like:

[Company_A]        [Company_B]      [Year]             [Deal_Type]

P&G                        Pepsi                   2006             Partnership

Coke                       Pepsi                   2007             Partnership

Pringles                   P&G                   2007             Partnership

Pringles                   P&G                    2008             M&A

there are bunch of similar records, and what I would like to do is two things:

1. Make a new column for the table named 'NumOfCoopsCompB' and count the number of deals of company_B in last 5 years (including 'Partnership' and 'M&A') prior to the deal year.

 for example, say, for Coke & Pepsi deal in year 2007, NumOfCoop would be marked as '1', since Pepsi had a deal with P&G in year 2006.

2. Make a new column for the table named 'NumOfCoopsCompACompB' and count the number of deals (this time only including 'Partnership' deals) between company_A and Company_B in last 5 years, prior to the focal deal year.

 for example, say Pringles & P&G deal in year 2008, NumOfCoopsCompACompB would be marked as '1', since in year 2006 they had a partnership deal.

Everytime I write a question on this board, I feel like I'm dumb, please help people.

[novinosrin]

Hi @jimmychoi 

For a start

You wrote- "this is how my data would look like:"

1. Should I assume that is expected output or input?

2. If not, where is your input data sample?

Please structure the question in the following way if you can 

1. Here is my data input sample

2. Here is the business(transformation) logic to process

3. Here is my expected output for the input sample

That's all we need. Thank you

PS Cleaner the question, it's easier to respond and saves time. 

[jimmychoi]

Hi, Novinosrin, I will structure my questions from next one. I wasn't good at questioning, making ambiguous requests, sorry 😞

[novinosrin]

Relax, nobody has ever been more dumb than me. 🙂

[DanielLangley]

data work.have;
attrib Company_A length = $50
		Company_B length = $50
		Year format = best4.
		Deal_Type length = $50
		;
infile datalines dlm = ",";
input Company_A $ Company_B $ Year Deal_Type $;
datalines;
P&G,Pepsi,2006,Partnership
Coke,Pepsi,2007,Partnership
Pringles,P&G,2007,Partnership
Pringles,P&G,2005,Partnership
Pringles,P&G,2008,M&A
;
run;

data work.have;
ID = _N_;
set work.have;
run;

proc sql;
create table work.want as
	select a.*
	,		sum(b.ID is not missing) as NumOfCoop
	,		sum(b.ID is not missing 
				and b.deal_type eq "Partnership"
				and (	( a.company_a = b.company_a and a.company_b = b.company_b )
					or 	( a.company_b = b.company_a and a.company_a = b.company_b ) )
				) as NumOfCoopsCompACompB 


	from work.have as a
	left join work.have as b on a.year ge b.year 
								and a.year - b.year le 5
								and (a.company_b = b.company_b or a.company_b = b.company_a)
	group by a.ID, a.company_a, a.company_b, a.year, a.deal_type
;
quit;

[jimmychoi]

Hi Daniel, thanks I have tried your codes but my system ended up saying: ERROR: Sort execution failure.

I have 132 gigs of free space in my local drive and will there be any other way to optimize the code?

[DanielLangley]

Ok. How many observations in the data? Are there any other Variables? How many companies are roughly in the data? Is it already sorted by anything?