topic Re: count times of appearance for elements in a data step array in SAS Programming

count times of appearance for elements in a data step array

aaronh — Mon, 21 Oct 2019 14:40:32 GMT

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,

Re: count distinct elements in a data step array

novinosrin — Mon, 21 Oct 2019 14:13:14 GMT

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;

Re: count distinct elements in a data step array

aaronh — Mon, 21 Oct 2019 14:16:16 GMT

Thanks novinosrin! They are actually longer names than just abcd in my datasets, sometimes as long as over 10 characters.

Re: count distinct elements in a data step array

novinosrin — Mon, 21 Oct 2019 14:17:19 GMT

Can you post a "comprehensive" sample plz

Re: count distinct elements in a data step array

aaronh — Mon, 21 Oct 2019 14:30:25 GMT

Sorry the extra blanks are not being shown in my text.

Re: count distinct elements in a data step array

ballardw — Mon, 21 Oct 2019 14:39:54 GMT

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

Re: count distinct elements in a data step array

novinosrin — Mon, 21 Oct 2019 14:41:53 GMT

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;

Re: count times of appearance for elements in a data step array

KachiM — Mon, 21 Oct 2019 15:29:38 GMT

@aaronh

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;

Re: count times of appearance for elements in a data step array

Tom — Mon, 21 Oct 2019 20:20:38 GMT

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;

Re: count times of appearance for elements in a data step array

aaronh — Tue, 22 Oct 2019 17:59:29 GMT

Thanks to everyone for your input!