To count number of firms given country year in panel data

[Elaine_S]

Hi, I am dealing with international data. I have four variables as follows. I would like to get the following variables and add the new variables at the end of my dataset.

1)  how many firms in each country.

2)  how many firms in each country each year. 

2)  the average sale in a given year. 

3)  the average sale in each year in each country

I try to do some coding as belows, but it does not work (no error reported, but in the new dataset, there is 0 obs)

can anyone help?  Many thanks.

data have;
input country $ year firm $ sales;
cards;

a 2000 a0201 20
a 2001 a0201 32
a 2002 a0201 31
a 2000 a0202 10
a 2001 a0202  9
a 2000 a0203  7.3
a 2001 a0203  4
a 2002 a0203  .
a 2003 a0203  2.9
b 2000 a0204  13
b 2001 a0204  13
b 2002 a0204  18
b 2003 a0204  17
b 2004 a0204 15
b 2000 a0205 33
b 2001 a0205  45
b 2002 a0205  32
b 2003 a0205   38

;
run;

proc sort data=have; by country year ;run;

data want;
set have;
by country year;
if first.firm then firm=1;else
count+1;
if last.firm and count>1 then output;
proc print; run;

[VDD]

Were you missing the firm in the sort?

Try this

data have;
input country $ year firm $ sales;
cards;
a 2000 a0201 20
a 2001 a0201 32
a 2002 a0201 31
a 2000 a0202 10
a 2001 a0202  9
a 2000 a0203  7.3
a 2001 a0203  4
a 2002 a0203  .
a 2003 a0203  2.9
b 2000 a0204  13
b 2001 a0204  13
b 2002 a0204  18
b 2003 a0204  17
b 2004 a0204 15
b 2000 a0205 33
b 2001 a0205  45
b 2002 a0205  32
b 2003 a0205   38
;
run;
 
proc sort data=have; by firm country year ;run;
data want;
set have;
by firm country year;
if first.firm then firm=1;else
count+1;
if last.firm and count>1 then output;
run;
proc print; run;

[Reeza]

1636
1637 data want;
1638 set have;
1639 by country year;
1640 if first.firm then firm=1;else
1641 count+1;
1642 if last.firm and count>1 then output;

NOTE: Numeric values have been converted to character
values at the places given by: (Line):(Column).
1640:25
NOTE: Variable first.firm is uninitialized.
NOTE: Variable last.firm is uninitialized.

They are not in red, but they are errors in your logic. 

---

@Elaine_S wrote:

Hi, I am dealing with international data. I have four variables as follows. I would like to get the following variables and add the new variables at the end of my dataset.

1)  how many firms in each country.

2)  how many firms in each country each year. 

2)  the average sale in a given year. 

3)  the average sale in each year in each country

I try to do some coding as belows, but it does not work (no error reported, but in the new dataset, there is 0 obs)

can anyone help?  Many thanks.

data have;
input country $ year firm $ sales;
cards;

a 2000 a0201 20
a 2001 a0201 32
a 2002 a0201 31
a 2000 a0202 10
a 2001 a0202  9
a 2000 a0203  7.3
a 2001 a0203  4
a 2002 a0203  .
a 2003 a0203  2.9
b 2000 a0204  13
b 2001 a0204  13
b 2002 a0204  18
b 2003 a0204  17
b 2004 a0204 15
b 2000 a0205 33
b 2001 a0205  45
b 2002 a0205  32
b 2003 a0205   38

;
run;

proc sort data=have; by country year ;run;

data want;
set have;
by country year;
if first.firm then firm=1;else
count+1;
if last.firm and count>1 then output;
proc print; run;

---

[Astounding]

Let's consider question 2, and how you would need to change your code to get counts of number of firms in each country year.  Right now, you are starting with:

proc sort data=have; by country year ;run;

data want;
set have;
by country year;
if first.firm then firm=1;else
count+1;
if last.firm and count>1 then output;

run;

With slight changes, you can get the answer to that question:

proc sort data=have; by country year ;run;

data want;
set have;
by country year;
if first.year then count=1;else
count+1;
if last.firm;

run;

[novinosrin]

if this by any chance not a datastep assignment and is allowed to be done using stat procedures, will make yours life easy and convenient.

Of course you would have to merge the results though