Here is a graph I just draw with the set of IDs managed by one of the two managers in a given Group.   All firms in Industry=1233 and Industry=1842 will get Contro=1 assigned. The reason why all firm IDs in Industry=2832 will get assigned Control=0 is that one of the managers was only controling a signle duplicate firm in that particular Industry and no other firms. This analysis is done at the Group level (i.e., distinct manager pair). It is possible that Manager 9999 may be in Group 1 and at the same time in Group 7, but the other Managers in Group 1 and 7 will be different (so the pairs are always distinct but the industry-manager pairs or industry-manager-firm_ID may not be distinct).  ... View more

Exactly, that is the correct hierarchy. But I would reverse the order to see a little bit:   There are multiple groups and each of these groups have a different pair of Managers (e.g., in Group 1 there are Managers 9999 and 8888, whereas in Group 3 there are Managers 9999  and 3333, so the pairs of managers are always distinct).  Each Group is composed of multiple industries, but a Manager may or may not serve in a given industry that belongs to that particular group (these industries are irrelevant and all rows with that industry should get a Control=0).  Within a given industry there may be several firms (whenever there is only one firm in a given industry, Control=0) and some of these firms are managed either BY one of them or both of the managers. It is possible that for a given industry, one of the managers may control only one ID and the other manager may control that particular iD as well in addition to managing other IDs. In this extreme example, all of the firms in that particular (firm) industry get a Control=0.  So the analysis is done at the Group-Industry level. Within each Group, there are only two distinct managers and some manager may or may not control IDs in a given industry. The key is to compare the IDs within a given industry that one manager controls to the IDs within the same industry (whenever avaiable in the same industry) that the other manager controls (any IDs of industries that are managed by only one manager get a Control=0).  Exactly, @Shmuel the rule to assing Control=1 is as follows:     - managers (two or more) in same industry with all different firm IDs                     ( assigned with control=1) -> The comparison of firm IDs is at the group-industry level, so there are at most two managers in a given Group-Industry pair and all manager pairs are distinct (so there are never duplicats at the Group+MGRNO+ID level). So it is correct, Control=1 for group-industries with all different firm IDs.      - managers in the same group and same industry(ies) but completely different IDs  (assigned with control=. ?)  -> In this case, we assign Control=1 whenever there are at least two identical firm IDs within the same group and same industry (but there could be also at least two identical firm IDs and other non-identical firm IDs within a given group-industry pair, we still assign Control=1). However, if one of the managers controls only one firm in a given group and industry pair (but no other firms in that particular group-industry) and at the other manager controls the same ID (regardless of whether she managers other different IDs or not within the same industry), then control=0 for all IDs in that particular group-industry. So whenever there is a duplicate firm ID in a group-industry pair and at least one of the managers controls that particular firm ID in that group-industry, then for all IDs in that group-industry Control=0. However, whenever there are at least two or more duplicate IDs in a given group-industry pair or there is only one firm ID duplicate in that particular group-industry but both managers control more than two firm IDs, then Control=1. Every other case, Control=0.    I have edited your answer, marked in red. I'll be able to deal with next week. Shmuel. ... View more

Thanks @gamotte. Your code does not take into account who the ID belongs to, so it just compares the distinct IDs within a given industry. However, what I am trying to do is as follows. Suppose there the two managers in the Group are A and B. Let's assume that in Industry=8321, Manager A owns firms with IDs 1234 and 2345, whereas Manager B owns 3514 and 7214. Then all of these rows will get a Column=1. Your code covers such cases. What I also need is the following case. Suppose in Inudstry=3152, Manager A owns firms with IDs 1884 and 1288, wheres Manager B owns firms with IDs 1884, 1288, and 8321. In this case, what I need is that Control=1 for all of these rows. This case makes it a little bit trick, otherwise I could have just counted distinct IDs by MGRNO and Industry and generate Control=1 if they are all distinct.  In conclusion, Control=1 for two cases: (1) Whenever IDs are distinct within a given Industry and for the MGRNO pair (not just the Group, but rather Group + MGRNO) and (2) Whenever there are duplicate IDs within a given Industry for the particular Group + MGRNO, then if the duplicates are at least two (e.g., 1884 and 1288), then all of the rows of that Industry get a 1 for that particular Group. In any other case, Control=0.  I am trying to be as clear as possible. Although it may seem simple at first glance, when these two rules (stated above) need to be considered for a given Group and the "distinct" / "distinction" has to be idenfitied by Group + MGRNO, then it becomes tricky. At least I got stuck. Thanks again for trying to help out.  ... View more

Thanks @Shmuel for your reply. That particular code does not work well in this setting since it just looks for industries that have at least two IDs whereas now I am trying to analyze whether a manager owns an ID in a given industry and the other manager holds a different ID from the same industry. In the current setting, I am trying to determine: Within a given group, do both managers have different IDs in a given industry? If yes, then Control=1 for those identified rows. However, the tricky part is that if a manager has an identical ID as the other manager but has no other IDs within the same industry, then Control=0 for all of the IDs of that industry (i.e., Control=0 for all of these identified rows that are in the same industry). For instance, Manager 1 may have ID 1234 and Manager 2 may have 1234 and 2341 where all of these IDs are in the same industry, then Control=0 for all of these rows. But if Manager 1 had 1234 and Manager 2 had only 2341, then Control=1. Or, if Manager 1 would have had 1234 and 2341, whereas Manager 2 had 4251 and 4159 (where all of these IDs are in the same industry), then Control=1. The simple case is that Control=1 whenever Manager 1 has completly different IDs compared to Manager 2 where both Managers are in the same Group. There is only one exception though making the case a little bit tricky, which is if there are at least two identical IDs (e.g., she owns 2351, 8451, and 9999) owned by a given Manager that the other Manager also owns in addition to other IDs (e.g., 2351, 8451, and 8521), then all rows get a Control=. This is the next case.    Within a given group, does one of the managers have at least two IDs which are in the same industry that the other manager also owns (i.e., do both managers have at least two identical IDs that are in the same Industry)? If yes, then Control=1 for those identified rows.   Else, Control=0.  Here is what the correct output looks like, I added comments about the cases described above next to the Controls:      DATA want; INPUT Mgrno Group ID Industry Control; DATALINES; 9999 1 00462610 1842 0 9999 1 01737210 1842 0 9999 1 12690310 2832 0 8888 1 12690310 2832 0 8888 1 12690311 2832 0 8888 1 37158620 2124 0 9999 1 23298321 4238 1 9999 1 53228424 4238 1 8888 1 53228424 4238 1 8888 1 23298321 4238 1 9999 1 12290311 1233 1 8888 1 01737211 1233 1 8888 1 99998881 1233 1 7777 2 12690310 2832 1 3333 2 32690311 2832 1 7777 2 37158620 2124 0 7777 2 38138222 3321 1 3333 2 44134422 3321 1 7777 2 53228424 4238 1 3333 2 77233421 4238 1 3333 2 12290311 1233 0 3333 2 93234320 1333 0 ; RUN;   UPDATED: Please see below a screenshot with the comments. When I past the code here, it becomes messy and not readable.   Here is the sample which is the same one above except that I removed the control and pair columns: DATA have; INPUT Mgrno Group ID Industry ; DATALINES; 9999 1 00462610 1842 9999 1 01737210 1842 9999 1 12690310 2832 8888 1 12690310 2832 8888 1 12690311 2832 8888 1 37158620 2124 9999 1 23298321 4238 9999 1 53228424 4238 8888 1 53228424 4238 8888 1 23298321 4238 9999 1 12290311 1233 8888 1 01737211 1233 8888 1 99998881 1233 7777 2 12690310 2832 3333 2 32690311 2832 7777 2 37158620 2124 7777 2 38138222 3321 3333 2 44134422 3321 7777 2 53228424 4238 3333 2 77233421 4238 3333 2 12290311 1233 3333 2 93234320 1333 ; RUN;     ... View more

This is very helpful, @Shmuel. I will give it a try. Thanks again for all of your valuable  suggestions. ... View more

  Thanks, @TarunKumar. I have tried your code with the following dataset, but the output seems incorrect: DATA work.sample; INPUT ID Year Industry; DATALINES; 004610 1983 . 004610 1984 6799 004610 1985 9999 004610 1986 . 004610 1987 . 004610 1988 8888 004610 1989 . 004610 1990 9999 ; RUN; ... View more

Thanks, @Shmuel. The code works very well. The retain command is very powerful, I have to make use of it more often!  The following part (let's call it part 1) of your code replaces missing observations with non-missing historical values: data temp; set have; by id ; retain ic; /* industry code */ if first.id then ic=industry; if industry=. then industry=ic; else ic=industry; drop ic; run; The last part of your code (let's call it part 2) replaces the the missing observations with non-missing future values: proc sort data=temp; by id descending year; run; data want; set temp; by id ; retain ic; /* industry code */ if first.id then ic=industry; if industry=. then industry=ic; else ic=industry; drop ic; run; Suppose I have the following data: DATA work.sample; INPUT ID Year Industry; DATALINES; 004610 1983 . 004610 1984 6799 004610 1985 9999 004610 1986 . 004610 1987 . 004610 1988 8888 004610 1989 . 004610 1990 9999 ; RUN; Whenever there is a future non-missing industry code and the code replaces missing industry code with that particular future non-missing idustry code, then we have the following output: DATA work.want1; INPUT ID Year Industry; DATALINES; 004610 1983 6799 004610 1984 6799 004610 1985 9999 004610 1986 8888 004610 1987 8888 004610 1988 8888 004610 1989 9999 004610 1990 9999 ; However, with your code the year=1989 is replaced with 8888 rather than 9999. Would you suggest just to remove the order of the code?  I.e., replace part 1 with part 2?   Update: @Shmuel, I just tried the reverse order (part 2, then part 1), but the output seems incorrect.  ... View more

  The following code yields the desired outcome:    proc sql; create table work.claims_out as select *, sum(Case when specialty=22 then 1 else 0 end) as Spec22, sum(Case when specialty=10 then 1 else 0 end) as Spec10, sum(Case when specialty=30 then 1 else 0 end) as Spec30 from work.claims group by client, year, quarter; quit; proc sort data=work.claims_out nodupkey; by client year quarter; quit;   ... View more

That is such a novel SQL code that identifies the correct observations! The distinct command is indeed very handy in this case. Thanks, @Ksharp.  ... View more

@Jagadishkatam, thanks. I have just tried your code with the following data but the output still does not look correct. In particular, observations with the ID 22222222 and 22222223 are selected, whereas they should have not been selected.  DATA work.sample; format group industry id control; INPUT ID Industry Group $ Control; DATALINES; 11111111 2542 AB 1 11111112 2542 AB 1 22222222 8062 AB 1 22222223 8062 AC 0 ; RUN; ... View more

I see. This makes A LOT of sense. So basically you are requiring to remove the observations (which are within the same group and industry) whenever they have the same ID (i.e., exactly as in the exampe you have given). Now I understand the code well! Thanks for all of your super helpful clarifications @Shmuel.  ... View more

Exactly, that's where I was facing an issue. Thanks for this clarification @Shmuel, I have just added a few comments and a question to the code below: DATA work.identified; set work.sample; by Group industry ID; retain lagID; if not (first.industry and last.industry); *Excludes unique observations within industry and group; if first.id and last.id; *Question: Not sure what this line exactly does. Does it state that if it's not a unique observation, then keep it? Then in the last two lines, the code checks whether or not the lagged observation is identical to the one considered?; if ID ^= lagID then output; *With the line below, this shifts the lagged value to the LagID column; lagID=ID; RUN;    ... View more