generate a table for each unique identifier but display frequencies.

[raniyah bakr]

I would like to generate a table that ranks universities in each [schoolyear] by unique identifier [id_concat]. I have struggled to generate tables that just count frequencies or rank on certain criteria so I am posing this question here. Below is an example of the relevant vars in my dataset. Thank you!

* Example generated by -dataex-. For more info, type help dataex
clear
input str100 college float id_concat str9 schoolyear
"Ilisagvik College"                                          1 "2017-2018"
"University of Alaska Fairbanks"                             2 "2017-2018"
"University of Alaska Anchorage"                             3 "2017-2018"
"University of Alaska Anchorage Matanuska Susitna College"   5 "2017-2018"
"Ilisagvik College"                                          8 "2017-2018"
"University of Alaska Fairbanks"                            11 "2017-2018"
"University of Alaska Anchorage"                            12 "2017-2018"
"University of Alaska Anchorage"                            14 "2017-2018"
"University of Alaska Fairbanks"                            19 "2017-2018"
"Ilisagvik College"                                         25 "2017-2018"
"University of Alaska Fairbanks"                            31 "2017-2018"
"Tyler Junior College"                                      33 "2017-2018"
"Fort Lewis College"                                        55 "2017-2018"
"Diablo Valley College"                                     58 "2017-2018"
"University of Alaska Anchorage"                            61 "2017-2018"
"Central Oregon Community College"                          63 "2017-2018"
"University of Alaska Fairbanks"                            67 "2017-2018"
"University of Alaska Fairbanks"                            64 "2017-2018"
"University of Alaska Fairbanks"                            66 "2017-2018"
"University of Alaska Fairbanks"                            74 "2017-2018"
"University of Alaska Anchorage"                            79 "2017-2018"
"Arizona State University"                                  84 "2017-2018"
"University of Alaska Anchorage"                            85 "2017-2018"
"George Fox University"                                     88 "2017-2018"
"University of Alaska Fairbanks"                            89 "2017-2018"
"University of Alaska Anchorage"                           104 "2017-2018"
"Ilisagvik College"                                         96 "2017-2018"
"Universal Technical Institute - Orlando"                  102 "2017-2018"
"University of Alaska Anchorage"                           108 "2017-2018"
"Charter College"                                          115 "2017-2018"
"University of Alaska Anchorage"                           122 "2017-2018"
"University of Washington"                                 124 "2017-2018"
"santa Rosa Junior College"                                125 "2017-2018"
"University of Washington"                                 130 "2017-2018"
"University of Alaska Fairbanks"                           137 "2017-2018"
"Liberty University"                                       138 "2017-2018"
"Skagit Valley College"                                    142 "2017-2018"
"Whitworth University"                                     156 "2017-2018"
"University of Alaska Anchorage"                           157 "2017-2018"
"Alaska Pacific University"                                159 "2017-2018"
"University of Alaska Anchorage"                           158 "2017-2018"
"Ilisagvik College"                                        166 "2017-2018"
"University of Alaska Anchorage"                           170 "2017-2018"
"University of Manchester"                                 171 "2017-2018"
"Texas Tech University"                                    184 "2017-2018"
"University of Alaska Anchorage"                           185 "2017-2018"
"Western Washington University"                            187 "2017-2018"
"Tanalian Aviation Inc."                                   188 "2017-2018"
"Ilisagvik College"                                        191 "2017-2018"
"Ilisagvik College"                                        193 "2017-2018"
"University of Alaska Anchorage"                           198 "2017-2018"
"University of Alaska Anchorage"                           200 "2017-2018"
"Ilisagvik College"                                        201 "2017-2018"
"Ilisagvik College"                                        202 "2017-2018"
"University of Alaska Fairbanks"                           211 "2017-2018"
"Boise State University"                                   217 "2017-2018"
"University of Idaho"                                      216 "2017-2018"
"City College of San Francisco"                            221 "2017-2018"
"Ilisagvik College"                                        222 "2017-2018"
"City College of San Francisco"                            219 "2017-2018"
"University of Alaska Fairbanks"                           227 "2017-2018"
"University of Alaska Anchorage"                           238 "2017-2018"
"University of Alaska Southeast"                           241 "2017-2018"
"University of Alaska Anchorage"                           240 "2017-2018"
"Olympic College"                                          245 "2017-2018"
"University of Alabama - Huntsville"                       249 "2017-2018"
"University of Hawaii at Manoa"                            250 "2017-2018"
"University of Alaska Anchorage"                           253 "2017-2018"
"Mt. Hood Community College"                               256 "2017-2018"
"University of Alaska Anchorage"                           268 "2017-2018"
"University of Alaska Fairbanks"                           275 "2017-2018"
"University of Alaska Fairbanks"                           289 "2017-2018"
"University of Alaska Anchorage"                           294 "2017-2018"
"University of Alaska Anchorage"                           295 "2017-2018"
"Saint Martins University"                                 297 "2017-2018"
"Montana State University"                                 307 "2017-2018"
"University of Alaska Anchorage"                           311 "2017-2018"
"Ilisagvik College"                                        310 "2017-2018"
"University of Alaska Anchorage"                           315 "2017-2018"
"Ilisagvik College"                                        332 "2017-2018"
"University of Alaska Anchorage"                           331 "2017-2018"
"Ilisagvik College"                                        328 "2017-2018"
"University of Alaska Anchorage"                           350 "2017-2018"
"University of Alaska Southeast"                           355 "2017-2018"
"University of Alaska Fairbanks"                           357 "2017-2018"
"Alaska Pacific University"                                360 "2017-2018"
"University of Alaska Anchorage"                           369 "2017-2018"
"University of Alaska Fairbanks"                           366 "2017-2018"
"University of Alaska Southeast"                           371 "2017-2018"
"University of Alaska Fairbanks"                           382 "2017-2018"
"University of Alaska Fairbanks"                           385 "2017-2018"
"University of Alaska Fairbanks"                           394 "2017-2018"
"University of Hawaii at Manoa"                            397 "2017-2018"
"Ilisagvik College"                                        399 "2017-2018"
"University of Alaska Fairbanks"                           412 "2017-2018"
"George Fox University"                                    417 "2017-2018"
"University of Alaska Anchorage"                           428 "2017-2018"
"University of Alaska Fairbanks"                           433 "2017-2018"
"University of Alaska Anchorage"                           434 "2017-2018"
"University of Alaska Anchorage"                           448 "2017-2018"
end

[raniyah bakr]

I should note that I have several school years

schoolyear

2017-2018

2018-2019

2019-2020

2020-2021

2021-2022

[raniyah bakr]

I would like to generate a table that ranks universities in each [schoolyear] by unique identifier [id_concat]. I have struggled to generate tables that just count frequencies or rank on certain criteria so I am posing this question here. Below is an example of the relevant vars in my dataset. Thank you!

* Example generated by -dataex-. For more info, type help dataex
clear
input str100 college float id_concat str9 schoolyear
"Ilisagvik College" 1 "2017-2018"
"University of Alaska Fairbanks" 2 "2017-2018"
"University of Alaska Anchorage" 3 "2017-2018"
"University of Alaska Anchorage Matanuska Susitna College" 5 "2017-2018"
"Ilisagvik College" 8 "2017-2018"
"University of Alaska Fairbanks" 11 "2017-2018"
"University of Alaska Anchorage" 12 "2017-2018"
"University of Alaska Anchorage" 14 "2017-2018"
"University of Alaska Fairbanks" 19 "2017-2018"
"Ilisagvik College" 25 "2017-2018"
"University of Alaska Fairbanks" 31 "2017-2018"
"Tyler Junior College" 33 "2017-2018"
"Fort Lewis College" 55 "2017-2018"
"Diablo Valley College" 58 "2017-2018"
"University of Alaska Anchorage" 61 "2017-2018"
"Central Oregon Community College" 63 "2017-2018"
"University of Alaska Fairbanks" 67 "2017-2018"
"University of Alaska Fairbanks" 64 "2017-2018"
"University of Alaska Fairbanks" 66 "2017-2018"
"University of Alaska Fairbanks" 74 "2017-2018"
"University of Alaska Anchorage" 79 "2017-2018"
"Arizona State University" 84 "2017-2018"
"University of Alaska Anchorage" 85 "2017-2018"
"George Fox University" 88 "2017-2018"
"University of Alaska Fairbanks" 89 "2017-2018"
"University of Alaska Anchorage" 104 "2017-2018"
"Ilisagvik College" 96 "2017-2018"
"Universal Technical Institute - Orlando" 102 "2017-2018"
"University of Alaska Anchorage" 108 "2017-2018"
"Charter College" 115 "2017-2018"
"University of Alaska Anchorage" 122 "2017-2018"
"University of Washington" 124 "2017-2018"
"santa Rosa Junior College" 125 "2017-2018"
"University of Washington" 130 "2017-2018"
"University of Alaska Fairbanks" 137 "2017-2018"
"Liberty University" 138 "2017-2018"
"Skagit Valley College" 142 "2017-2018"
"Whitworth University" 156 "2017-2018"
"University of Alaska Anchorage" 157 "2017-2018"
"Alaska Pacific University" 159 "2017-2018"
"University of Alaska Anchorage" 158 "2017-2018"
"Ilisagvik College" 166 "2017-2018"
"University of Alaska Anchorage" 170 "2017-2018"
"University of Manchester" 171 "2017-2018"
"Texas Tech University" 184 "2017-2018"
"University of Alaska Anchorage" 185 "2017-2018"
"Western Washington University" 187 "2017-2018"
"Tanalian Aviation Inc." 188 "2017-2018"
"Ilisagvik College" 191 "2017-2018"
"Ilisagvik College" 193 "2017-2018"
"University of Alaska Anchorage" 198 "2017-2018"
"University of Alaska Anchorage" 200 "2017-2018"
"Ilisagvik College" 201 "2017-2018"
"Ilisagvik College" 202 "2017-2018"
"University of Alaska Fairbanks" 211 "2017-2018"
"Boise State University" 217 "2017-2018"
"University of Idaho" 216 "2017-2018"
"City College of San Francisco" 221 "2017-2018"
"Ilisagvik College" 222 "2017-2018"
"City College of San Francisco" 219 "2017-2018"
"University of Alaska Fairbanks" 227 "2017-2018"
"University of Alaska Anchorage" 238 "2017-2018"
"University of Alaska Southeast" 241 "2017-2018"
"University of Alaska Anchorage" 240 "2017-2018"
"Olympic College" 245 "2017-2018"
"University of Alabama - Huntsville" 249 "2017-2018"
"University of Hawaii at Manoa" 250 "2017-2018"
"University of Alaska Anchorage" 253 "2017-2018"
"Mt. Hood Community College" 256 "2017-2018"
"University of Alaska Anchorage" 268 "2017-2018"
"University of Alaska Fairbanks" 275 "2017-2018"
"University of Alaska Fairbanks" 289 "2017-2018"
"University of Alaska Anchorage" 294 "2017-2018"
"University of Alaska Anchorage" 295 "2017-2018"
"Saint Martins University" 297 "2017-2018"
"Montana State University" 307 "2017-2018"
"University of Alaska Anchorage" 311 "2017-2018"
"Ilisagvik College" 310 "2017-2018"
"University of Alaska Anchorage" 315 "2017-2018"
"Ilisagvik College" 332 "2017-2018"
"University of Alaska Anchorage" 331 "2017-2018"
"Ilisagvik College" 328 "2017-2018"
"University of Alaska Anchorage" 350 "2017-2018"
"University of Alaska Southeast" 355 "2017-2018"
"University of Alaska Fairbanks" 357 "2017-2018"
"Alaska Pacific University" 360 "2017-2018"
"University of Alaska Anchorage" 369 "2017-2018"
"University of Alaska Fairbanks" 366 "2017-2018"
"University of Alaska Southeast" 371 "2017-2018"
"University of Alaska Fairbanks" 382 "2017-2018"
"University of Alaska Fairbanks" 385 "2017-2018"
"University of Alaska Fairbanks" 394 "2017-2018"
"University of Hawaii at Manoa" 397 "2017-2018"
"Ilisagvik College" 399 "2017-2018"
"University of Alaska Fairbanks" 412 "2017-2018"
"George Fox University" 417 "2017-2018"
"University of Alaska Anchorage" 428 "2017-2018"
"University of Alaska Fairbanks" 433 "2017-2018"
"University of Alaska Anchorage" 434 "2017-2018"
"University of Alaska Anchorage" 448 "2017-2018"
end

I realize this was a poorly constructed title and request. That is probably the reason I am struggling to return the desired result when I attempt to tabulate. I have used tab, summarize() to get close! My sticking point is to isolate schoolyears. How does one incorporate an if statement into the tab, sum command? Thank you for any help!!

[raniyah bakr]

I figured it out! Thanks self [tab college if schoolyear=="2017-2018", sum(id_concat)]

[raniyah bakr]

I figured it out! Thanks self [tab college if schoolyear=="2017-2018", sum(id_concat)]

I am wrong, again. This result does not tell me how many unique people attend these colleges. I have the unique identifier... can anyone help me figure out how many unique 'unique identifiers' go to each college, and by schoolyear? It seems like a multiway table is the direction I should be headed. Thank you!

[raniyah bakr]

My clunky solution for those following along is this. If anyone has a more concise solution, I am all ears. Thank you.

table (college id_concat) (schoolyear)if college=="University of Alaska Anchorage" | college=="University of Alaska Fairbanks"

[Nick Cox]

This sounds like the kind of problem where one key word is distinct rather than unique. A 2008 paper is the fullest survey I know (below) and that mentioned a new distinct command. Just yesterday I submitted a revision of distinct. But many problems boil down to something this.

.  webuse nlswork , clear
(National Longitudinal Survey of Young Women, 14-24 years old in 1968)

. * how many distinct grades in each occupation code?

. egen tag = tag(grade occ_code)

. egen ndistinct = total(tag), by(occ_code)

. tabdisp occ_code, c(ndistinct)

----------------------
Occupatio |
n         |  ndistinct
----------+-----------
        1 |         12
        2 |         12
        3 |         15
        4 |         13
        5 |         16
        6 |         18
        7 |         14
        8 |         18
        9 |          4
       10 |         14
       11 |         13
       12 |          2
       13 |         11
        . |          0
----------------------

SJ-20-4 dm0042_3  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q4/20   SJ 20(4):1028--1030
        sort() option has been added

SJ-15-3 dm0042_2  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q3/15   SJ 15(3):899
        improved table format and display of large numbers of
        observations

SJ-12-2 dm0042_1  . . . . . . . . . . . . . . . . Software update for distinct
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q2/12   SJ 12(2):352
        options added to restrict output to variables with a minimum
        or maximum of distinct values


SJ-8-4  dm0042  . . . . . . . . . . . .  Speaking Stata: Distinct observations
        (help distinct if installed)  . . . . . .  N. J. Cox and G. M. Longton
        Q4/08   SJ 8(4):557--568
        shows how to answer questions about distinct observations
        from first principles; provides a convenience command

[raniyah bakr]

This sounds like the kind of problem where one key word is distinct rather than unique. A 2008 paper is the fullest survey I know (below) and that mentioned a new distinct command. Just yesterday I submitted a revision of distinct. But many problems boil down to something this.

. webuse nlswork , clear
(National Longitudinal Survey of Young Women, 14-24 years old in 1968)

. * how many distinct grades in each occupation code?

. egen tag = tag(grade occ_code)

. egen ndistinct = total(tag), by(occ_code)

. tabdisp occ_code, c(ndistinct)

----------------------
Occupatio |
n | ndistinct
----------+-----------
1 | 12
2 | 12
3 | 15
4 | 13
5 | 16
6 | 18
7 | 14
8 | 18
9 | 4
10 | 14
11 | 13
12 | 2
13 | 11
. | 0
----------------------

SJ-20-4 dm0042_3 . . . . . . . . . . . . . . . . Software update for distinct
(help distinct if installed) . . . . . . N. J. Cox and G. M. Longton
Q4/20 SJ 20(4):1028--1030
sort() option has been added

SJ-15-3 dm0042_2 . . . . . . . . . . . . . . . . Software update for distinct
(help distinct if installed) . . . . . . N. J. Cox and G. M. Longton
Q3/15 SJ 15(3):899
improved table format and display of large numbers of
observations

SJ-12-2 dm0042_1 . . . . . . . . . . . . . . . . Software update for distinct
(help distinct if installed) . . . . . . N. J. Cox and G. M. Longton
Q2/12 SJ 12(2):352
options added to restrict output to variables with a minimum
or maximum of distinct values


SJ-8-4 dm0042 . . . . . . . . . . . . Speaking Stata: Distinct observations
(help distinct if installed) . . . . . . N. J. Cox and G. M. Longton
Q4/08 SJ 8(4):557--568
shows how to answer questions about distinct observations
from first principles; provides a convenience command

Nick, I appreciate this. I am going to tinker with it. I did not know about distinct,,, this is incredibly helpful. Thanks again.

[raniyah bakr]

This worked like a charm, thanks again. apologies for the limited code, I was working from my dofile. Here is a sample:

 tabdisp college schoolyear, c(ndistinct )

------------------------------------------------------------------------------------------------
                                         |                      School Year                     
                                 College | 2017-2018  2018-2019  2019-2020  2020-2021  2021-2022
-----------------------------------------+------------------------------------------------------
               Academy of Art University |                    1          1          1           
                  Adams State University |                               3          3          3
Adams State University 208 Edgemont Blvd |                                                     1
                   Alaska Career College |                    2                     2          1
                Alaska Christian College |                    4          4          4          4
               Alaska Pacific University |        18         18         18         18         18
                 Alaska Technical Center |         2                     2                      
      Alaska Vocational Technical Center |                    1          2          2           
        Alliant International University |         1          1          1          1           
                     American University |                                          1          1
                          Angel Aviation |                    1                                 
                Arizona State University |         7          7          7                     7
                      Ashford University |         2          2          2          2           
                Austin Community College |                               1          1          1
              Bellevue Community College |                               1                      
                     Bellevue University |                               1

[raniyah bakr]

wait, this table is still not correct. [ndistinct] should vary every [schoolyear]. I am now understanding that the [cellvar] option displays the observation of the variable in parentheses. This could be a much simpler command....