Hello experts,
Suppose I have a dataset as follows:
data have;
input var1$ 1-12 var2$ 13-24 var3$ 25-36 var4$ 37-48;
datalines;
ElpasoDiner Amazon Amazon WilliamsS
MickeyD SecondCare Bluecross MickeyD
ElpasoDiner ElpasoDiner Amazon United
;
I am struggling to come up with a way to count the distinct elements in var1-var4 (using an array anyways). Is there a way to get count like this:
ElpasoDiner Amazon WilliamsS MickeyD SecondCare Bluecross United
1 2 1 0 0 0 0
0 0 0 2 1 1 0
2 1 0 0 0 0 1
The example I gave here is an ideal situation, but in my real dataset I often do not know the contents in each row (i.e. I do not know exactly what is in var1 - var4). And there are multiple possible text strings for var1 - var4.
Many thanks,
Okay a generic one and linear simple safe method I think
/*1st pass to get distinct values in a macor var list*/
data _null_;
do _n_=1 by 1 until(z);
set have end=z;
array v var:;
array t(99)$32 _temporary_;/*Assign length of 32 which is variable name maximun length*/
do over v;
if v not in t then do;
n+1;
t(n)=v;
end;
end;
end;
call symputx('list',catx(' ',of t(*)));
run;
%put &=list;
/*2nd pass linear look up */
data want;
set have;
array v(*) var:;
array t(*) &list;
do i=1 to dim(t);
t(i)=0;
do j =1 to dim(v);
if vname(t(i))=v(j) then t(i)=sum(t(i),1);
end;
end;
drop i j;
run;
are they single char values as your sample suggests?
Assuming your sample is truly representative, it;s easy
data have;
input var1 $ var2 $ var3 $ var4 $;
datalines;
a a b c
a b c d
a a a b
;
data want;
set have;
array t a b c d;
_k=cats(of var:);
do over t;
t=countc(_k,strip(vname(t)));
end;
drop _:;
run;
Can you post a "comprehensive" sample plz
Okay a generic one and linear simple safe method I think
/*1st pass to get distinct values in a macor var list*/
data _null_;
do _n_=1 by 1 until(z);
set have end=z;
array v var:;
array t(99)$32 _temporary_;/*Assign length of 32 which is variable name maximun length*/
do over v;
if v not in t then do;
n+1;
t(n)=v;
end;
end;
end;
call symputx('list',catx(' ',of t(*)));
run;
%put &=list;
/*2nd pass linear look up */
data want;
set have;
array v(*) var:;
array t(*) &list;
do i=1 to dim(t);
t(i)=0;
do j =1 to dim(v);
if vname(t(i))=v(j) then t(i)=sum(t(i),1);
end;
end;
drop i j;
run;
@aaronh wrote:
Hello experts,
Suppose I have a dataset as follows:
data have;
input var1$ 1-12 var2$ 13-24 var3$ 25-36 var4$ 37-48;
datalines;
ElpasoDiner Amazon Amazon WilliamsS
MickeyD SecondCare Bluecross MickeyD
ElpasoDiner ElpasoDiner Amazon United
;
I am struggling to come up with a way to count the distinct elements in var1-var4 (using an array anyways).
The example I gave here is an ideal situation, but in my real dataset I often do not know the contents in each row (i.e. I do not know exactly what is in var1 - var4). And there are multiple possible text strings for var1 - var4.
Many thanks,
You really need to show what you expect the output to look like for a given example input.
Another way is to read individual Vars into a Data Set. You can specify the length of the Var as a macro.
Then use Proc Freq.
%let Vlen=12;
data have;
infile datalines;
input ;
/* put _infile_; */
length Var $&Vlen;
do i = 1 to countw(_infile_, ' ');
Var = scan(_infile_, i);
output;
end;
drop i;
datalines;
ElpasoDiner Amazon Amazon WilliamsS
MickeyD SecondCare Bluecross MickeyD
ElpasoDiner ElpasoDiner Amazon United
;
run;
proc freq data = have;
tables Var / out = FreqCount nopercent nocum;
run;
You need to have some row id to generate the expected results of the counts per row, instead of overall counts.
If there isn't one then add one during the transpose step.
data tall ;
set have;
array list var1-var4 ;
row+1;
do col=1 to dim(list);
word = list[col];
output;
end;
run;
proc freq data=tall;
tables row*word / out=counts;
run;
Thanks to everyone for your input!
AH
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: