topic count the number of distinct values with two same identifiers in SAS Data Management

count the number of distinct values with two same identifiers

lauren11 — Fri, 12 Apr 2019 12:54:06 GMT

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!

Re: count the number of distinct values with two same identifiers

Reeza — Fri, 12 Apr 2019 15:18:22 GMT

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!

Re: count the number of distinct values with two same identifiers

lauren11 — Thu, 25 Apr 2019 18:45:57 GMT

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.

Re: count the number of distinct values with two same identifiers

Reeza — Sat, 27 Apr 2019 03:35:59 GMT

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.

Re: count the number of distinct values with two same identifiers

TomKari — Sun, 28 Apr 2019 01:50:37 GMT

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