count the number of distinct values with two same identifiers

[lauren11]

Hi guys, I'm new to SAS and is doing a research on cross ownership. A short version of my data looks like follows:

Firm          Year_Quarter    Industry    Manager

00282410     1980Q1               116            001

00282410     1980Q2               116            002   

00807310     1980Q1               120            001

00807310     1980Q3               120            003

41321630     1981Q2               116            002

41321630     1981Q3               116            002 

What I want to do is to create a new variable called NumConnected , which is, for each firm in each Year_Quarter, the number of same-industry firms that also have same manager id. My ideal result table would look like follows:

Firm          Year_Quarter    Industry    Manager    NumConnected

00282410     1980Q1               116            001              0

00282410     1980Q2               116            002              1

00807310     1980Q1               120            001              0

00807310     1980Q3               120            003              0

41321630     1980Q2               116            002               0

41321630     1980Q3               116            002               1

How should I do this in SAS?  Thanks very much!

[Reeza · Accepted solution]

You say by "for each firm in each Year_Quarter" but those go across quarters below in your example?

And why is the second last row not flagged as well? 

Firm          Year_Quarter    Industry    Manager    NumConnected

00282410     1980Q1               116            001              0

00282410     1980Q2               116            002              1

00807310     1980Q1               120            001              0

00807310     1980Q3               120            003              0

41321630     1980Q2               116            002               0

41321630     1980Q3               116            002               1

Assuming you want the manager counts by industry then, I would do something like the following:

proc sort data=have; by industry manager;
run;

data want;
set have;
by industry manager;
NumConnected =  not (first.manager and last.manager) ;
run;

---

@lauren11 wrote:

Hi guys, I'm new to SAS and is doing a research on cross ownership. A short version of my data looks like follows:

Firm          Year_Quarter    Industry    Manager

00282410     1980Q1               116            001

00282410     1980Q2               116            002   

00807310     1980Q1               120            001

00807310     1980Q3               120            003

41321630     1981Q2               116            002

41321630     1981Q3               116            002 

What I want to do is to create a new variable called NumConnected , which is, for each firm in each Year_Quarter, the number of same-industry firms that also have same manager id. My ideal result table would look like follows:

Firm          Year_Quarter    Industry    Manager    NumConnected

00282410     1980Q1               116            001              0

00282410     1980Q2               116            002              1

00807310     1980Q1               120            001              0

00807310     1980Q3               120            003              0

41321630     1980Q2               116            002               0

41321630     1980Q3               116            002               1

How should I do this in SAS?  Thanks very much!

---

[lauren11]

Thank you REEZA, that gives me some inspiration. The code I later came up with is like the following:

proc sql;
create table ownship.test2 as
select *, count(distinct (case when block>=0.01 then firm end))as NumConnected1
from ownship.test1
group by manager,industry,Year_quarter;

quit;

data ownship.test2;set ownship.test2;
if NumConnected1>=1 then NumConnectedQ=NumConnected1-1;
else NumConnectedQ=0;
run;

Does it make sense to you?

thanks.

[Reeza]

Run it both ways and use PROC COMPARE to see if you get the same results. Personally, I would highly recommend reading and understanding BY group processing. It's insanely powerful and will save you hours of time later on. SQL doesn't have the same concepts at all so it often takes several SQL queries to get what you can do in one SAS data set.

[TomKari]

Reeza's advice is really good here (as always).

I've seen a lot of cases of SQL being bent out of shape and totally unreadable to try to do something that is much easier in a different programming paradigm. I'm not suggesting to not use SQL, just to use it where it provides the best solution.

Tom