How do i count the number of subjects in a panel data by year

[Ahmad]

i have a panel data set like this, how can i count the number of stocks in each year??

DATA auto ;
  input stock $ PRICE year ;
DATALINES;
AMC 4099 22 1
AMC 4749 17 2

AMC 3799 22 3
Audi 9690 17 1
Audi 6295 23 2
BMW 9735 25 1
Buick 4816 20 1
Buick 7827 15 3

Buick 5788 18 1
Buick 4453 26 2
Buick 5189 20 3
Buick 10372 16 4
Buick 4082 19 5
Cad. 11385 14 1
Cad. 14500 14 2
Cad. 15906 21 3
;
RUN;

[art297]

Not sure what your question is or what is actually in your data.  Guessing that the next to the last field is number of stocks, and then you just want the totals for each year, how about?:

DATA auto ;

  input stock $ PRICE number year ;

DATALINES;

AMC 4099 22 1

AMC 4749 17 2

AMC 3799 22 3

Audi 9690 17 1

Audi 6295 23 2

BMW 9735 25 1

Buick 4816 20 1

Buick 7827 15 3

Buick 5788 18 1

Buick 4453 26 2

Buick 5189 20 3

Buick 10372 16 4

Buick 4082 19 5

Cad. 11385 14 1

Cad. 14500 14 2

Cad. 15906 21 3

;

RUN;

proc sql;

  select distinct sum(number) as total_stocks,year

    from auto

      group by year

        order by year

  ;

quit;

[Ahmad]

hi thanks for the reply, please ignore the number column in the data, let le give you an example

DATA auto ;

  input stock $ PRICE date year ;

DATALINES;

a 23 02jan2006 2006

a 27 03jan2006 2006

a 37 04feb2006 2006

b 12 01jan2006 2006 

b 15 02feb2006 2006

a 26 02jan2007 2007

a 20 03jan2007 2007

b 17 01jan2007 2007 

b 14 02feb2007 2007

c 78 04jan2007 2007

c 24 14feb2008 2008

;

run;

Now i want to count number of stocks traded each year i.e. 2 in year 2006 and 3 in year 2007, how do i do it in sas for a large panel data for - years, with many many stocks

[Tom · Accepted solution]

Is SAS SQL code you can use the DISTINCT keyword inside of the COUNT() aggregate function.

proc sql noprint ;

create table counts as

   select year,count(distinct stock) as n_stocks

   from auto

   group by year

;

quit;

[Ahmad]

thanks Tom works perfect