SAS newbie trying to tally

[briannascott]

I'm just beginning to learn SAS and this is what I'm trying to do and would appreciate any help I can find.

In my data, each member has an observation I would like to tally the number of times VAR1 occurs when it matches another observation with the same VAR1 and also VAR2.

So for the following data, there would be 2 matches ob1/ob2 and ob4/ob5/ob6, for a count of 2.

ob1 VAR1=1 VAR2=1

ob2 VAR1=1 VAR2=1

ob3 VAR1=1 VAR2=2

ob4 VAR1=2 VAR2=2

ob5 VAR1=2 VAR2=2

ob6 VAR1=2 VAR2=2

[ballardw]

You don't show what you expect for output.

I might try

Proc freq data=have;
   where var1 = var2;
   tables var1*var2 /list;
run;

which will count all of the combinations of var1 and var2. If you have missing values and want to include those add the Missing option.

[briannascott]

thanks but that doesn't do it.

I want to count (tally?) how many times VAR1 matches another observation, so where VAR1=VAR1

and also meets the other condition - where both observations have the same VAR2.

So the output would just be a count of 2 in the example.

[ballardw]

Provide an actual example of how the output is supposed to look.

[briannascott]

It would be a count, it wouldn't be a frequency of observations.

A count of how many times there are matching VAR1s that also have the same VAR2

i'm sorry if I'm not explaining it clearly enough.

[Kurt_Bremser]

So you mean

proc sql;
select count(*)
from have
where var1 = var2;
quit;

?

[Reeza]

More likely this:

proc sql;
select count(distinct var1) as Count
from have
where var1 = var2;
quit;

[briannascott]

where var1=var1 of a different observation.

that's what i'm trying to count in the data.

[Reeza]

That's not what's shown in the sample data. Please post a better example of what you have and what you want.

[Kurt_Bremser]

---

@briannascott wrote:

where var1=var1 of a different observation.

that's what i'm trying to count in the data.

---

proc sql;
select count(var1)
from have
group by var1;
quit;

or

proc freq data=have;
tables var1;
run;

[PaigeMiller]

---

@briannascott wrote:

It would be a count, it wouldn't be a frequency of observations.

A count of how many times there are matching VAR1s that also have the same VAR2

i'm sorry if I'm not explaining it clearly enough.

---

Adding to @ballardw 's earlier answer

Proc freq data=have;
   where var1 = var2;
   tables var1*var2 /noprint out=counts;
run;
proc means data=counts n;
    var count;
run;

[PaigeMiller]

PROC FREQ would output 2 records, that's your answer, 2.

[Reeza]

You need to expand your example then to incorporate that.

I would suggest showing a table of what you have and for that data set as input, exactly what you expect as output. Your current example is unfortunately too simple as there are multiple ways to easily get your stated answer of 2.

[briannascott]

obs1 VAR1=A VAR2=1

obs2 VAR1=A VAR2=1

obs3 VAR1=B VAR2=2

obs4 VAR1=B VAR2=2

obs5 VAR1=B VAR2=2

obs6 VAR1=C VAR2=1

obs7 VAR1=C VAR2=1

obs8 VAR1=C VAR2=2

obs9 VAR1=D VAR2=1

The output would simply be a count of "3"

Count 1 - obs1 and obs2 match on VAR1 as both have a value of A and also match on VAR2 with value of 1

Count 2 - obs3, obs4, obs5 match on VAR1=B, match on VAR2=2

Count 3 - obs6 and obs7 match on VAR1=C, VAR2=1 

I don't care about obs8 since it didn't match VAR2 with obs6

I don't care about obs9 since it doens't match VAR1 with any

[Kurt_Bremser]

proc sql;
select var1, var2, count(*) as count
from have
group by var1, var2
having count > 1;
quit;
%put &sqlobs.;