topic Re: How to count for specific observations? in SAS Procedures

How to count for specific observations?

Shayan2012 — Mon, 05 Nov 2012 01:10:11 GMT

Hi guys,

I need to count the number of observations for "each firm" in "each year" to be able to ignore those years with less than (for example) 3 observations.

my data set is something like this (and the count column is what I need!)

year permno count

1 1 3

2 1 1

3 1 1

1 2 1

2 2 4

3 2 1

4 2 1

can any one help me to write this with SQL this, please? (and since I am a newbie in sas, would you please describe it a little ?)

Thanks a lot

Re: How to count for specific observations?

PGStats — Mon, 05 Nov 2012 01:43:57 GMT

proc sql;

create table want as

select * from have natural join

(select *, count(*) as count

from have

group by permno, year

/* having count(*) >=3 */ ) ;

select * from want;

quit;

Says : Count observations within each group defined by permno and year (that's the part within parenthesis) then join these counts with the original table, joining on the variables that they have in common (natural join).

The commented out (having clause) is where you would exclude years with less than 3 observations .

Re: How to count for specific observations?

Shayan2012 — Mon, 05 Nov 2012 04:55:09 GMT

That worked like a charm. Thanks a lot PG

Re: How to count for specific observations?

Linlin — Mon, 05 Nov 2012 14:10:08 GMT

Hi PG,

What is natural join? I have used left, right, inner join, but have never used natural join. I am not too lazy to read. Your expanation would help me to remeber better.

Updated after Art's comments:smileysilly:.

Thank you!

Re: How to count for specific observations?

art297 — Mon, 05 Nov 2012 14:33:32 GMT

Linlin: don't be lazy! Read!

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473691.htm

Re: How to count for specific observations?

PGStats — Mon, 05 Nov 2012 14:46:10 GMT

natural join is like inner join with an implicit ON clause on all the variables that have the same name in both tables. You can also do a natural left join.

Re: How to count for specific observations?

Tom — Mon, 05 Nov 2012 15:03:36 GMT

Actually the SAS specific implementation of SQL helps with this type of query. Try this program.

proc sql noprint ;

create table want as

select *,count(*) as count

from have

group by year, permno

;

quit;

Notice the log message :

NOTE: The query requires remerging summary statistics back with the original data.

So you get all of the original data and the new COUNT variable is merged on so that it has the same value for all observations for that year/permno combination. You can add a HAVING clause to eliminate the observations from year/permno combinations with insufficient counts.

proc sql noprint ;

create table want as

select *,count(*) as count

from have

group by year, permno

having calculated count > 2

;

quit;

Re: How to count for specific observations?

PGStats — Mon, 05 Nov 2012 15:11:35 GMT

That doesn't work Tom if the dataset contains only variables year and permno. In that case you get normal summarization without remerging.

Re: How to count for specific observations?

Linlin — Mon, 05 Nov 2012 15:18:08 GMT

Thank you Art and PG!

The expanation I get from you would help me to understand better.

Re: How to count for specific observations?

Tom — Mon, 05 Nov 2012 15:28:21 GMT

In that case use the new COUNT variable as a FREQ or WEIGHT variable in future analysis and save the disk space of store multiple copies of the group by variables.

Re: How to count for specific observations?

Tal — Mon, 05 Nov 2012 22:53:51 GMT

hey guys,

what does select *,do in the above example? Can someone please explain

i mean i know about select var1,var2,..... from table or select*from table but i haven't seen this above before

Re: How to count for specific observations?

art297 — Mon, 05 Nov 2012 23:13:55 GMT

It is just asking for all of the variables in the table. Since it is followed by count(*) as count, between the two statements it is asking for all of the variables as well as a count of the number of records.

Re: How to count for specific observations?

Tal — Mon, 05 Nov 2012 23:19:37 GMT

thanks Arthur

Re: How to count for specific observations?

thdang — Wed, 07 Nov 2012 12:49:58 GMT

if i also want to take the average of those specific observation and excluding the one which the average less than e.g. n. how can I do that?

Thanks

Re: How to count for specific observations?

Shayan2012 — Thu, 08 Nov 2012 18:26:40 GMT

I think this might work for you:

assume you want to take an average for variable VAR1

Proc sql;

create want as

select *, avg(VAR1) as average

from have

where average gt N;

It would be great if expert guys mention whether it works or not!

Re: How to count for specific observations?

thdang — Thu, 08 Nov 2012 20:41:37 GMT

thank you very much. But can i combine this

Proc sql;

create want as

select *, avg(VAR1) as average

from have

where average gt N;

and this eg

proc sql noprint ;

create table want as

select *,count(*) as count

from have

group by year, permno

having calculated count > 2

;

quit;

to have 1 proc insteads of two