Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

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

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

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
Cad. 11385 14 1
Cad. 14500 14 2
Cad. 15906 21 3
;
RUN;


Accepted Solutions
Solution
‎11-17-2012 03:34 PM
Super User
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;

View solution in original post


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

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;

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
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 832 views
  • 1 like
  • 3 in conversation