BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
adnany
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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 . . . .

View solution in original post

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

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
Calcite | Level 5

@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 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 596 views
  • 1 like
  • 2 in conversation