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;
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;
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;
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
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;
thanks Tom works perfect
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.