Counting the number of ID type for each country in the data

[Aamina Khurraa]

Hi,

My Panel data has 260 Panel IDs(firms) over a number of time period and another variable names as ID_Type (which is either 1 or 2 for each ID(firm)) and a third variable stating total 12 countries that the IDs belong to. The data looks like this:


Time IDs ID_Type Country
-- A IFI Pak
--- B CFI Pak
--- C IFI Bahrain
-- D IFI India
--- E CFI India
--- F IFI Pakistan

I want produce the table where the number of IDS and ID-Types can be represented for each country.such as a Table like:

Country ID IFI CFI
Pak 3 2 1

Bahrain 1 1 0

India 2 1 1
All the tables that I have made show the number of observations for IDs and ID-Types. But I need to count how many unique ID_types exist in each county. I was able to make such table some time ago but now am not able to replicate again. My previous table that I want to make again is given below:

Country	IDs	CFI	IFI
Bahrain	15	8	7
Bangladesh	30	29	1
Egypt	13	11	2
Indonesia	36	23	13
Jordan	33	28	5
Kuwait	38	18	20
Malaysia	71	31	40
Pakistan	56	42	14
Qatar	15	9	6
KSA	18	10	8
Singapore	18	17	1
Total	376	250	126

[Marcos Almeida]

Please use (as per FAQ) code delimiters or dataex to share data.

As you may already know, this is the best approach to entail a helpful reply in the forum.

That said, if by "table" you mean generating counts before tabulating, the command - egen - is the one to look for.

[Aamina Khurraa]

I have tried to used egen But could not get around what I wanted to do. Also Previously when I made this table, I didn't use any egen function. I just extracted the table as an asdoc output.

[Wouter Wakker]

If there is a way to do it without egen I would be interested to know about it. Here's a way to do it with egen:

egen tag = tag(Country IDs)
tab Country ID_Type if tag

[Nick Cox]

https://www.stata-journal.com/articl...article=dm0042 discusses principles.

But most problems to do with distinct values (a much better term than "unique") that require new variables one way or another seem soluble by egen's tag() function, sometimes in conjunction with total(). See especially p.563 of the linked paper.

[Aamina Khurraa]

Thanks Wouter Wakker

The tag code did work perfect this time but I do remember I never used this before. I will let you know once I find out what way I achieved the same without using tag or distinct.

Best