topic Re: Counting the number of specific rows in SAS Programming

Counting the number of specific rows

jimmychoi — Fri, 01 Feb 2019 13:09:18 GMT

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.

Re: Counting the number of specific rows

novinosrin — Fri, 01 Feb 2019 15:03:16 GMT

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.

Re: Counting the number of specific rows

DanielLangley — Fri, 01 Feb 2019 15:19:09 GMT

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;

Re: Counting the number of specific rows

jimmychoi — Fri, 01 Feb 2019 15:55:55 GMT

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

Re: Counting the number of specific rows

novinosrin — Fri, 01 Feb 2019 15:56:45 GMT

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

Re: Counting the number of specific rows

jimmychoi — Fri, 01 Feb 2019 22:58:09 GMT

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?

Re: Counting the number of specific rows

DanielLangley — Mon, 04 Feb 2019 09:23:58 GMT

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?