Solved
Contributor
Posts: 43

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

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
;
RUN;

Accepted Solutions
Solution
‎11-17-2012 03:34 PM
Super User
Posts: 7,079

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

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;

All Replies
PROC Star
Posts: 7,492

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

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

;

RUN;

proc sql;

select distinct sum(number) as total_stocks,year

from auto

group by year

order by year

;

quit;

Contributor
Posts: 43

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

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

Solution
‎11-17-2012 03:34 PM
Super User
Posts: 7,079

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

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;

Contributor
Posts: 43

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

thanks Tom works perfect

🔒 This topic is solved and locked.