topic Re: SAS newbie trying to tally in SAS Programming

SAS newbie trying to tally

briannascott — Mon, 29 Jun 2020 17:20:44 GMT

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

Re: SAS newbie trying to tally

ballardw — Mon, 29 Jun 2020 17:23:58 GMT

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.

Re: SAS newbie trying to tally

briannascott — Mon, 29 Jun 2020 17:32:50 GMT

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.

Re: SAS newbie trying to tally

ballardw — Mon, 29 Jun 2020 17:37:54 GMT

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

Re: SAS newbie trying to tally

PaigeMiller — Mon, 29 Jun 2020 17:39:14 GMT

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

Re: SAS newbie trying to tally

Reeza — Mon, 29 Jun 2020 17:43:46 GMT

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.

Re: SAS newbie trying to tally

briannascott — Mon, 29 Jun 2020 17:44:43 GMT

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.

Re: SAS newbie trying to tally

Kurt_Bremser — Mon, 29 Jun 2020 17:52:13 GMT

So you mean

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

Re: SAS newbie trying to tally

Reeza — Mon, 29 Jun 2020 17:55:31 GMT

More likely this:

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

Re: SAS newbie trying to tally

briannascott — Mon, 29 Jun 2020 18:02:06 GMT

where var1=var1 of a different observation.

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

Re: SAS newbie trying to tally

Reeza — Mon, 29 Jun 2020 18:10:21 GMT

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

Re: SAS newbie trying to tally

Kurt_Bremser — Mon, 29 Jun 2020 18:19:28 GMT

@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;

proc freq data=have;
tables var1;
run;

Re: SAS newbie trying to tally

PaigeMiller — Mon, 29 Jun 2020 18:22:54 GMT

@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;

Re: SAS newbie trying to tally

briannascott — Mon, 29 Jun 2020 18:24:44 GMT

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

Re: SAS newbie trying to tally

Kurt_Bremser — Mon, 29 Jun 2020 18:29:56 GMT

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

Re: SAS newbie trying to tally

Reeza — Mon, 29 Jun 2020 19:15:12 GMT

My last SQL code is what you want then.