topic Re: Count unique variables associated with one firm in Statistical Procedures

Count unique variables associated with one firm

dlazer1 — Sun, 01 Oct 2017 05:46:10 GMT

Hi,

I have my data set and need to do a count for unique analysts following a company. They are given random numbers but all I need is a final count.

So far I have this but it is not working.

Thank you for your help!

proc means data=rawdata.ibes noprint;
count=1 if analys="";
var count;
by cusip fpedats;
output out=forecastdate sum(count)=total;
run;

Re: Count unique variables associated with one firm

Norman21 — Sun, 01 Oct 2017 08:10:54 GMT

The following might help you find an answer:

"Count the distinct values of a variable"

http://support.sas.com/kb/36/898.html

Re: Count unique variables associated with one firm

Reeza — Sun, 01 Oct 2017 17:35:44 GMT

There are two methods illustrated here:

/*This demonstrates how to count the number of unique occurences of a variable
across groups. It uses the SASHELP.CARS dataset which is available with any SAS installation.
The objective is to determine the number of unique car makers by origin/

Note: The SQL solution can be off if you have a large data set and these are not the only two ways to calculate distinct counts.
If you're dealing with a large data set other methods may be appropriate.*/

*Count distinct IDs;
proc sql;
create table distinct_sql as
select origin, count(distinct make) as n_make
from sashelp.cars
group by origin;
quit;

*Double PROC FREQ;
proc freq data=sashelp.cars noprint;
table origin * make / out=origin_make;
run;

proc freq data=origin_make noprint;
table origin / out= distinct_freq;
run;

title 'PROC FREQ';
proc print data=distinct_freq;
run;
title 'PROC SQL';
proc print data=distinct_sql;
run;

https://github.com/statgeek/SAS-Tutorials/blob/master/count_distinct_by_group

Re: Count unique variables associated with one firm

dlazer1 — Mon, 02 Oct 2017 20:12:52 GMT

Thank you. I now used a proc freq as follows.

proc freq data=sortedibes noprint order=freq;
tables CUSIP*ANALYS*FPEDATS /out=counts;
run;

proc means data=counts max;
class CUSIP ANALYS;
var COUNT;
run;

How do I get the total value for unique ANALYS for a particular cusip on a particular date with is FPEDATS?

Thanks!

Re: Count unique variables associated with one firm

Reeza — Mon, 02 Oct 2017 22:31:36 GMT

Use a WHERE statement to restrict your output.