Tabulating or listing distinct values for multiple variables

[Santosh Dash]

Hi,

I want to tabulate or list the distinct or unique values of "stcode" and "avg" variables. The dataex is given below.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 state float(year income) str2 stcode
"Kerala"    2016  1 "KL"
"Kerala"    2017  2 "KL"
"Kerala"    2018  3 "KL"
"Kerala"    2019  4 "KL"
"Odisha"    2016 10 "OD"
"Odisha"    2017 20 "OD"
"Odisha"    2018 30 "OD"
"Odisha"    2019 40 "OD"
"Bihar"     2016  7 "BR"
"Bihar"     2017  8 "BR"
"Bihar"     2018 10 "BR"
"Bihar"     2019 11 "BR"
"Karnataka" 2016  5 "KA"
"Karnataka" 2017 15 "KA"
"Karnataka" 2018 20 "KA"
"Karnataka" 2019 24 "KA"
end

bys stcode : egen avg = mean(income)

I want to list the as follows:

+--------------+
| stcode avg |
|--------------|
1. | BR 9 |
2. | KA 16 |
3. | KL 2.5 |
4. | OD 25 |
+--------------+

The above list is generated after dropping the duplicates.

Any advice will be highly appreciated.

Thank you.

[Noah Mkasanga]

tabstat income, by(stcode) nototal

[Santosh Dash]

Thank you, Noah Mkasanga

If I want the following table, how to generate it?

+--------------------+
| state stcode |
|--------------------|
1. | Kerala KL |
2. | Odisha OD |
3. | Bihar BR |
4. | Karnataka KA |
+--------------------+

The above list is generated after dropping the duplicates.

Thank you.

[Fei Wang]

To #3, one way is

duplicates drop state stcode, force
list state stcode

. list state stcode

     +--------------------+
     |     state   stcode |
     |--------------------|
  1. |    Kerala       KL |
  2. |    Odisha       OD |
  3. |     Bihar       BR |
  4. | Karnataka       KA |
     +--------------------+

[Øyvind Snilsberg]

g st_stcode = state + " " + stcode
tabstat income, by(st_stcode) nototal

[Nick Cox]

You could use the groups commands from the Stata Journal. No changes are needed to the data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 state float(year income) str2 stcode
"Kerala"    2016  1 "KL"
"Kerala"    2017  2 "KL"
"Kerala"    2018  3 "KL"
"Kerala"    2019  4 "KL"
"Odisha"    2016 10 "OD"
"Odisha"    2017 20 "OD"
"Odisha"    2018 30 "OD"
"Odisha"    2019 40 "OD"
"Bihar"     2016  7 "BR"
"Bihar"     2017  8 "BR"
"Bihar"     2018 10 "BR"
"Bihar"     2019 11 "BR"
"Karnataka" 2016  5 "KA"
"Karnataka" 2017 15 "KA"
"Karnataka" 2018 20 "KA"
"Karnataka" 2019 24 "KA"
end

groups state stcode, show(none)

  +--------------------+
  |     state   stcode |
  |--------------------|
  |     Bihar       BR |
  | Karnataka       KA |
  |    Kerala       KL |
  |    Odisha       OD |
  +--------------------+

In naming groups (back in 2003) I borrowed a common English word. A side-effect is that search works best given an otherwise unpredictable code used in publication:

. search st0496, entry

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

SJ-18-1 st0496_1  . . . . . . . . . . . . . . . . . Software update for groups
        (help groups if installed)  . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/18   SJ 18(1):291
        groups exited with an error message if weights were specified;
        this has been corrected

SJ-17-3 st0496  . . . . .  Speaking Stata: Tables as lists: The groups command
        (help groups if installed)  . . . . . . . . . . . . . . . .  N. J. Cox
        Q3/17   SJ 17(3):760--773
        presents command for listing group frequencies and percents and
        cumulations thereof; for various subsetting and ordering by
        frequencies, percents, and so on; for reordering of columns;
        and for saving tabulated data to new datasets

For a quick overview, see https://www.statalist.org/forums/for...updated-on-ssc

[Santosh Dash]

Fei Wang, thank you for the suggestion. The table in #3 is generated as you have suggested in #4.

Doing this removes the data from the memory. This can be taken care of by using preserve and restore.

However, is it possible to list two variables (state and stcode) without dropping the duplicates or without using preserve and restore?

[Fei Wang]

Fei Wang, thank you for the suggestion. The table in #3 is generated as you have suggested in #4.

Doing this removes the data from the memory. This can be taken care of by using preserve and restore.

However, is it possible to list two variables (state and stcode) without dropping the duplicates or without using preserve and restore?

Yes. But it generates a redundant variable and is not as neat as Nick's solution in #6.

. bys state: gen wanted = _n == 1

. list state stcode if wanted

     +--------------------+
     |     state   stcode |
     |--------------------|
  1. |     Bihar       BR |
  5. | Karnataka       KA |
  9. |    Kerala       KL |
 13. |    Odisha       OD |
     +--------------------+

[Santosh Dash]

Nick Cox, Thank you. That is the solution I was looking for.
@Øyvind Snilsberg, Thank you.

Fei Wang, Thank you for the help. Your solution in #8 was what I was looking for.