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.
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.
Relax, nobody has ever been more dumb than me. 🙂
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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.