topic Re: Filtering data by frequency in SAS Enterprise Guide

Filtering data by frequency

JNK1 — Mon, 12 May 2014 01:54:40 GMT

Hi all,

I am trying to filter down a list of all listed US companies and their dividend payouts over 10 years, to a list of all listed US companies which have at least one dividend payout over the 10 year period.

I have managed to filter out any companies which do not operate over the full 10 years by doing the following;

PROC SQL;

CREATE TABLE DDM AS

SELECT LPERMNO, datadate, fyear, csho, dvc, ni

FROM work.data

GROUP BY 1

HAVING COUNT(*)=12;

QUIT;

Which took me long enough in itself, yet this list is not all that I require as the companies must also be paying dividends.

Is there a way of filtering the data subject to the above, but also subject to the criteria that AT LEAST 1 'dvc' is > 0 for each LPERMNO (Company)?

I do not want to get rid of all LPERMNO's which have a 'dvc' = 0, just the LPERMNO's where no 'dvc's exist.

Please let me know if more clarification is required.

Re: Filtering data by frequency

art297 — Mon, 12 May 2014 02:31:00 GMT

If you want companies for which you have at least ten years data, why do you select where count(*)=10?

I would think that you'd want count(*) ge 10 and max(dvc) gt 0

Re: Filtering data by frequency

JNK1 — Mon, 12 May 2014 02:46:43 GMT

Sorry, my data has already been collected and filtered to be from December 1997 to December 2008, hence why there are 12 observations per company (LPERMNO).

max(dvc) GT 0 did the trick though, thank you so much for your help!