Quartz | Level 8

## Frequency on each value in multiple variables.

Hello,

I have the following dataset.

I need to find out how many times does "a"(or b,c,d,e,f) appear jointly in var_1, var_2 and var_3.

``````data temp;
input _ID var_1 \$ var_2 \$ var_3 \$;
datalines;
1 a b c
2 d e f
3 b d e
4 c d b
5 e f a
6 a c f
7 b d e
8 c a d
9 d b c
10 e f b
11 f e a
12 a b c
13 d e f
14 b d e
15 c d b
16 e f a
17 a c f
18 b d e
19 c a d
20 d b c
21 e f b
22 f e a
23 a d b
24 b e c
25 c f d
;
run;``````
1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## Re: Frequency on each value in multiple variables.

It's possible you need to explain more about what is in the data and what you want.  Looking at the post, I'm going to assume that each observation never repeats the same letter.  In that case:

proc freq data=have noprint;

tables var_1 / out=var1_counts (rename=(var_1=value) drop=percent);

tables var_2 / out=var2_counts(rename=(var_2=value) drop=percent);

tables var_3 / out=var3_counts (rename=(var_3=value) drop=percent);

run;

data want;

merge var1_counts (rename=(count=count1))

var2_counts (rename=(count=count2))

var3_counts (rename=(count=count3));

by value;

total_count = sum(count1, count2, count3);

run;

It's untested code at this point, so you'll need to test it to see how close it comes.

9 REPLIES 9
Super User

PROC Star

## Re: Frequency on each value in multiple variables.

It's possible you need to explain more about what is in the data and what you want.  Looking at the post, I'm going to assume that each observation never repeats the same letter.  In that case:

proc freq data=have noprint;

tables var_1 / out=var1_counts (rename=(var_1=value) drop=percent);

tables var_2 / out=var2_counts(rename=(var_2=value) drop=percent);

tables var_3 / out=var3_counts (rename=(var_3=value) drop=percent);

run;

data want;

merge var1_counts (rename=(count=count1))

var2_counts (rename=(count=count2))

var3_counts (rename=(count=count3));

by value;

total_count = sum(count1, count2, count3);

run;

It's untested code at this point, so you'll need to test it to see how close it comes.

Quartz | Level 8

## Re: Frequency on each value in multiple variables.

Thank You @Astounding.

I totally see your point here. I am wondering if there is any alternate way to doing these frequencies together rather than treating them separately and then joining them.

The output is what i want though. So thank you again.

Diamond | Level 26

## Re: Frequency on each value in multiple variables.

@david27 wrote:

Hello,

I have the following dataset.

I need to find out how many times does "a"(or b,c,d,e,f) appear jointly in var_1, var_2 and var_3.

How many times does an "a" appear jointly?

Meaning if var_1='a' and var_2='a' and var_3='a' then this counts as 'a' appears one time?

``````data temp;
input _ID var_1 \$ var_2 \$ var_3 \$;
jointly=cats(var_1,var_2,var_3);
count_a=find(jointly,'a')>0;
count_b=find(jointly,'b')>0;
count_c=find(jointly,'c')>0;
count_d=find(jointly,'d')>0;
count_e=find(jointly,'e')>0;
count_f=find(jointly,'f')>0;
datalines;
1 a b c
2 d e f
3 b d e
4 c d b
5 e f a
6 a c f
7 b d e
8 c a d
9 d b c
10 e f b
11 f e a
12 a b c
13 d e f
14 b d e
15 c d b
16 e f a
17 a c f
18 b d e
19 c a d
20 d b c
21 e f b
22 f e a
23 a d b
24 b e c
25 c f d
;
run;

proc freq data=temp;
tables count_a--count_f;
run;``````

--
Paige Miller
Super User

## Re: Frequency on each value in multiple variables.

Can you clearly define what "jointly" means in this context?

Also if your actual values are more complex than single letters now is the time to provide such data. There are solutions that may work for single letters that may not work for more complex string values.

Quartz | Level 8

## Re: Frequency on each value in multiple variables.

"jointly" meaning how many times does value "a" appear in var_1 and Var_2 and Var_3.

I don't mean to concatenate any variables.

Super User

## Re: Frequency on each value in multiple variables.

I would transpose the data and then do a PROC FREQ. It's more dynamic so it doesn't matter how many columns or values you have.

``````data have;
input _ID var_1 \$ var_2 \$ var_3 \$;
datalines;
1 a b c
2 d e f
3 b d e
4 c d b
5 e f a
6 a c f
7 b d e
8 c a d
9 d b c
10 e f b
11 f e a
12 a b c
13 d e f
14 b d e
15 c d b
16 e f a
17 a c f
18 b d e
19 c a d
20 d b c
21 e f b
22 f e a
23 a d b
24 b e c
25 c f d
;
run;
proc transpose data=have out=long;
by _ID;
var var_1-var_3;
run;

proc freq data=long;
table col1*_name_ / out=want;
run;``````

@david27 wrote:

Hello,

I have the following dataset.

I need to find out how many times does "a"(or b,c,d,e,f) appear jointly in var_1, var_2 and var_3.

``````data temp;
input _ID var_1 \$ var_2 \$ var_3 \$;
datalines;
1 a b c
2 d e f
3 b d e
4 c d b
5 e f a
6 a c f
7 b d e
8 c a d
9 d b c
10 e f b
11 f e a
12 a b c
13 d e f
14 b d e
15 c d b
16 e f a
17 a c f
18 b d e
19 c a d
20 d b c
21 e f b
22 f e a
23 a d b
24 b e c
25 c f d
;
run;``````

Tourmaline | Level 20

## Re: Frequency on each value in multiple variables.

Hi @david27

``````
proc transpose data=temp out=w;
by _id;
var var_:;
run;

proc sql;
create table w1 as
select col1, count(col1) as c
from w
group by _name_,col1
order by col1;
quit;
data want;
do _n_=1 by 1 until(last.col1);
set w1;
by col1;
array var_count(3);
var_count(_n_)=c;
end;
total_count=sum(of var_count(*));
drop c;
run;
``````
Super User

## Re: Frequency on each value in multiple variables.

It is easy for SAS/IML .

``````data temp;
input _ID var_1 \$ var_2 \$ var_3 \$;
datalines;
1 a b c
2 d e f
3 b d e
4 c d b
5 e f a
6 a c f
7 b d e
8 c a d
9 d b c
10 e f b
11 f e a
12 a b c
13 d e f
14 b d e
15 c d b
16 e f a
17 a c f
18 b d e
19 c a d
20 d b c
21 e f b
22 f e a
23 a d b
24 b e c
25 c f d
;
run;

proc iml;
use temp(keep=var_:);
read all var _char_ into x;
call tabulate(level,freq,x);
create want var {level freq};
append;
close;
quit;

proc print;run;``````
Discussion stats
• 9 replies
• 924 views
• 2 likes
• 7 in conversation