topic Re: How do i count the number of subjects in a panel data by year in SAS Data Science

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

Ahmad — Sat, 17 Nov 2012 15:21:43 GMT

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;

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

art297 — Sat, 17 Nov 2012 20:16:54 GMT

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;

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

Ahmad — Sat, 17 Nov 2012 20:25:31 GMT

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

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

Tom — Sat, 17 Nov 2012 20:34:58 GMT

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;

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

Ahmad — Sat, 17 Nov 2012 20:39:26 GMT

thanks Tom works perfect