identifying a common observation across the groups

[kamalesh pahurkar]

How can I identify the common observations across the groups. I have a group codes under which there are different codes. I want to identify the codes that are common across the groups.

[Carlo Lazzaro]

Kamalesh:
If I got you right, you may want to consider something along the following lines:

. use "https://www.stata-press.com/data/r17/nlswork.dta"
(National Longitudinal Survey of Young Women, 14-24 years old in 1968)

. egen wanted=count( year) if year==70

[Eric Makela]

Hi Kamalesh,

This is effective for creating categorical variables for what you describe, even if not terribly elegant. The variable 'group3' identifies variables that are common across the first two groups. Note there are none. I don't wish to speak for Carlo, but he would suggest a more detailed description of your problem is necessary to properly contribute to Statalist and solve your actual problem.

sysuse auto
gen wtclass=(weight<2700)+2*(weight>=2700 & weight<3500)+3*(weight>=3500)
gen mpgclass=(mpg<20)+2*(mpg>=20 & mpg<28)+3*(mpg>=28)
egen group1=group(wtclass foreign)
egen group2=group(make mpgclass)
egen group3=group(group1 group2)

[kamalesh pahurkar]

Hi look, I have the following kind of data structure. I just want to keep or identify the product codes that are common across all the company code.

company code	product code
11	115
11	117
11	119
11	112
12	117
12	115
12	119
16	108
16	117
16	119
16	111
16	115
16	120
14	222
14	117
14	115
14	119

[Hemanshu Kumar]

Perhaps this?

clear
input int(company_code product_code)
11    115
11    117
11    119
11    112
12    117
12    115
12    119
16    108
16    117
16    119
16    111
16    115
16    120
14    222
14    117
14    115
14    119
end

egen int num_companies = count(company_code), by(product_code)
levelsof company_code, local(companies)
local tot_companies: word count `companies'

gen byte common_to_all = (num_companies == `tot_companies')

Then you can do things like

. tab product_code if common_to_all

product_cod |
          e |      Freq.     Percent        Cum.
------------+-----------------------------------
        115 |          4       33.33       33.33
        117 |          4       33.33       66.67
        119 |          4       33.33      100.00
------------+-----------------------------------
      Total |         12      100.00

This code assumes that each product_code appears only once for a given company_code. This is the case in your example, but if it is not true in the dataset, let me know and we'll find an alternative solution.

[kamalesh pahurkar]

Hi Hemanshu,Thannks for your response. It works. Can you tell me, what will be the change in syntax if I also have a time variable in the above dataset.

[Zanda Bullock]

I have a similar problem, though slightly more involved. Note that id is a string variable in my data, weeks are groups.
The data has the following structure:

week id species species_diff
11 115 79
11 117 98
11 119 71
11 112 52
12 116 68
12 115 96 17
12 119 61 -10
13 108 94
13 117 72
13 119 54 -7
13 111 50
13 115 86 -10
13 120 60
14 222 68
14 117 63 -9
14 115 91 5
14 119 84 30

I want to generate variable species_diff.

To get there, stata needs to:
match id 115 in week 12 with the same id 115 in week 11 (if there is none, do not look into week 10)
if a perfect match is found between two subsequent weeks, then calculate difference of variable species
record the difference as a newly generated variable species_diff.

Any advice would be helpful.

[Hemanshu Kumar]

You could do:

clear
input int week  str3 id int(species species_diff)
11 115 79   .
11 117 98   .
11 119 71   .
11 112 52   .
12 116 68   .
12 115 96   17
12 119 61 -10
13 108 94   .
13 117 72   .
13 119 54 -7
13 111 50   .
13 115 86 -10
13 120 60   .
14 222 68   .
14 117 63 -9
14 115 91 5
14 119 84 30
end

bysort id (week): gen wanted = cond(week - week[_n-1] == 1, species - species[_n-1], .)

which produces:

. list, noobs sepby(id) abbrev(15)

  +----------------------------------------------+
  | week    id   species   species_diff   wanted |
  |----------------------------------------------|
  |   13   108        94              .        . |
  |----------------------------------------------|
  |   13   111        50              .        . |
  |----------------------------------------------|
  |   11   112        52              .        . |
  |----------------------------------------------|
  |   11   115        79              .        . |
  |   12   115        96             17       17 |
  |   13   115        86            -10      -10 |
  |   14   115        91              5        5 |
  |----------------------------------------------|
  |   12   116        68              .        . |
  |----------------------------------------------|
  |   11   117        98              .        . |
  |   13   117        72              .        . |
  |   14   117        63             -9       -9 |
  |----------------------------------------------|
  |   11   119        71              .        . |
  |   12   119        61            -10      -10 |
  |   13   119        54             -7       -7 |
  |   14   119        84             30       30 |
  |----------------------------------------------|
  |   13   120        60              .        . |
  |----------------------------------------------|
  |   14   222        68              .        . |
  +----------------------------------------------+

[Zanda Bullock]

Many thanks, Kumar.

This works well, if group (week) variable is numerical.

With weeks having format 28apr2013, 05may2013, .. it may require a bit different approach or adjust the date format.

[Hemanshu Kumar]

Zanda Bullock , this is the sort of thing why Statalist's FAQ (see #12) encourage you to post a data extract using the dataex command. My best guess is that your data has week actually stored in numerical format but the display format is set to %td. If this is the case, the code I provided should work as-is. If however, your week variable is actually stored as a string, we may need a couple lines of code to convert it to numeric. Please post a data extract, as specified.

[Zanda Bullock]

Indeed, my mistake. I wanted to keep the data example as close as possible to the original example in this thread.

clear
input str16 symbol double marketcap float date
"ADA"    35153676037.03 22689
"BNB"    65862892979.34 22689
"BTC"   799950408997.28 22689
"ETH"   344772260795.07 22689
"LUNA"   20575427609.16 22689
"SOL"    29629765453.55 22689
"USDC"   52360369755.16 22689
"USDT"   78458775425.11 22689
"XRP"    38757348317.38 22689
"ADA"    20976354319.88 23431
"AVAX"   14125113515.93 23431
"BNB"    58105243467.07 23431
"BTC"  1015926591604.22 23431
"ETH"   374009336729.27 23431
"SOL"    45631391775.01 23431
"USDC"   28180925305.61 23431
"USDT"   97901318957.18 23431
"XRP"    29613270171.03 23431
"BNB"   181370523134.29 24026
"BTC"  2295654718660.58 24026
"DOGE"   31444744190.76 24026
"ETH"   502659982281.55 24026
"SOL"   107950809429.22 24026
"TRX"    30592522539.16 24026
"USDC"    75671935493.8 24026
"USDT"  180021595871.23 24026
"XRP"   151884632916.48 24026
end
format %td date

[Hemanshu Kumar]

Great, so your date variable is already numeric, it's merely set to display in a human-readable form, as dictated by format %td. If you were intending to look at the difference in marketcap between successive dates, something like this should work:

bysort symbol (date): gen wanted = cond(date - date[_n-1] == 1, marketcap - marketcap[_n-1], .)

If however, you want to work with weeks instead, you might find it useful to first do

gen week = wofd(date)
format week %tw

and then use the variable week in place of date in the preceding code. I tried this with your data example, but it produces only missing values since it never has data for the same symbol from successive weeks. But it should work fine in your actual dataset assuming it has such situations.

[Zanda Bullock]

Fantastico. With 'week' it works straight away.

PS With 'date' it does not work for some reason, tried several ways (the syntax correct, but generates an empty variable - (missing values generated for all obs)). Will investigate later.

[Nick Cox]

Note that wofd() uses Stata's idiosyncratic definition of week, that week 1 of any year always starts on 1 January, week 2 on 8 January, and so on until week 52 starts on 24 December and is 9 or 8 days long depending on whether the year is leap or not. For example,

. clear 

. set obs 10
Number of observations (_N) was 0, now 10.

. gen date = mdy(12, 21, 2025) + _n

. format date %td

. gen week = wofd(date)

. list, sepby(week)

     +------------------+
     |      date   week |
     |------------------|
  1. | 22dec2025   3430 |
  2. | 23dec2025   3430 |
     |------------------|
  3. | 24dec2025   3431 |
  4. | 25dec2025   3431 |
  5. | 26dec2025   3431 |
  6. | 27dec2025   3431 |
  7. | 28dec2025   3431 |
  8. | 29dec2025   3431 |
  9. | 30dec2025   3431 |
 10. | 31dec2025   3431 |
     +------------------+

I've never yet seen a Stata user say that this is what they want or how their data were produced.

For much more, search for code on epiweeks or read within

. search week, sj

Search of official help files, FAQs, Examples, and Stata Journals

SJ-25-2 dm0116  . . Speaking Stata: Nine notes on dealing with dates and times
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q2/25   SJ 25(2):471--483                                (no commands)
        overview of several key points in dealing with date and time
        data in Stata

SJ-22-2 dm0107_1  . . .  Erratum: Stata tip 145: Numbering weeks within months
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q2/22   SJ 22(2):465--466                                (no commands)
        errata for tip on numbering weeks within months

SJ-22-1 dm0107  . . . . . . . . . Stata tip 145: Numbering weeks within months
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/22   SJ 22(1):224--230                                (no commands)
        tip on numbering weeks within months

SJ-19-3 dm0100  . . . . . . . . . .  Speaking Stata: The last day of the month
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/19   SJ 19(3):719--728                                (no commands)
        discusses three related problems about getting the last day
        of the month in a new variable

SJ-12-4 dm0065_1  . . . . . Stata tip 111: More on working with weeks, erratum
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/12   SJ 12(4):765                                     (no commands)
        lists previously omitted key reference

SJ-12-3 dm0065  . . . . . . . . . .  Stata tip 111: More on working with weeks
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/12   SJ 12(3):565--569                                (no commands)
        discusses how to convert data presented in yearly and weekly
        form to daily dates and how to aggregate such data to months
        or longer intervals

SJ-10-4 dm0052  . . . . . . . . . . . . . . . . Stata tip 68: Week assumptions
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q4/10   SJ 10(4):682--685                                (no commands)
        tip on Stata's solution for weeks and on how to set up
        your own alternatives given different definitions of the
        week