Identifying secondary peers

[adnany]

Hi all,

I have data on firm id (e.g., 1,2,3), Year (e.g., 2015, 2016) and SIC membership (Standard Industrial Codes), such that each firm may be a member of multiple SIC codes in a given year. These SIC codes represent a firm’s primary peers - that is, firms with which it competes directly by being present in these SIC codes each year. Also, a firm’s SIC membership may vary from year to year (e.g., firm 1 competes across 6 SICs in 2015 but only 3 in 2016).

I want to construct variables for secondary peer SIC codes (SPeer1, SPeer2… SPeern) for each firm in each year – that is, those SIC codes in which a firm does not directly compete (that is, a firm does not belong to these SIC codes in a year), but its primary peers compete with other firms in these SIC codes. For example, secondary peer SIC codes for firm 1 in 2015 are 4517, 4518, 4519, 4521 and 4522 because firm 1 does not directly compete in thee SIC codes in the year 2015, but its primary peers, firm 2 (competing with firm 1 in 4512 and 4516) and firm 3 (competing with firm 1 in 4513) compete with other firms in these SIC codes in 2015.

F	Year	SIC1	SIC2	SIC3	SIC4	SIC5	SIC6	SPeer1	SPeer2	SPeer3	SPeer4	SPeer5
1	2015	4511	4512	4513	4514	4515	4516	4517	4518	4519	4521	4522
2	2015	4512	4516	4517	4518	4519		4511	4513	4514	4515
3	2015	4513	4520	4521	4522			4511	4512	4516	4518
1	2016	4511	4512	4513				4518	4519
2	2016	4512	4519					4511	4513
3	2016	4513	4518					4511	4512

Will appreciate if you may help me with a code to construct the secondary peer SIC Codes.

Regards,

Adnan

[ChrisNZ · Accepted solution]

1.Please provide data in usable form. For example see here.

2. Another example where a string aggregation functions would be very useful. Come on SAS, just do it!

3. This seems to work:

data HAVE;
 infile cards missover;
 input F YEAR SIC1 SIC2 SIC3;
 cards;
1 2016 4511 4512 4513
2 2016 4512 4519
3 2016 4513 4518
run;

proc sql;
  create table CARTESIAN as
  select a.*, b.F as BF, b.SIC1 as S1, b.SIC2 as S2, b.SIC3 as S3  
  from HAVE a
      ,HAVE b
  where a.YEAR eq b.YEAR
      & a.F    ne b.F
      & ( (a.SIC1 & (a.SIC1=S1 | a.SIC1=S2 | a.SIC1=S3))
        | (a.SIC2 & (a.SIC2=S1 | a.SIC2=S2 | a.SIC2=S3)) 
        | (a.SIC3 & (a.SIC3=S1 | a.SIC3=S2 | a.SIC3=S3))
        )
  order by a.F, a.YEAR; 
quit;

data WANT;
  set CARTESIAN;
  by F YEAR;
  array SA[*] SIC1-SIC3;
  array SB[*] S1-S3;
  array SPEER[6];
  retain SPEER:;
  do I=1 to dim(SB) while ( SB[I] ); 
    if ^index(catx('|',of SA[*]), cats(SB[I])) & ^index(catx('|',of SPEER[*]), cats(SB[I])) then do; 
      IDX+1;                                     
      SPEER[IDX]=SB[I];             
    end;
  end;
  if last.YEAR then do;
     output;
     call missing(of SPEER[*], IDX);
  end;
  drop I IDX BF S1-S3 ;
run;

proc print noobs;run;

F	YEAR	SIC1	SIC2	SIC3	SPEER1	SPEER2	SPEER3	SPEER4	SPEER5	SPEER6
1	2016	4511	4512	4513	4518	4519	.	.	.	.
2	2016	4512	4519	.	4511	4513	.	.	.	.
3	2016	4513	4518	.	4511	4512	.	.	.	.

[adnany]

@ChrisNZ  thank you. This solution worked for me after building on your code to cater for up to 20 SIC codes in each row and up to 200 secondary peers.    

regards,

Adnan