Count Fields Across

[kz134]

Hi everyone,

I have this table:

Field1	Field2	Field3	Field4	Field5	Field6
AAA	AAA	DIF	VMX	IRU	BBB
VCX	KEM	CCC	BBB	AAA	AAA
EIE	OFP	MCN	UVN	DDK	JDN
…	…	…	…	…	…

I also have a lookup table:

Field

AAA

BBB

CCC

…

I want to count the fields that contain the fields in the lookup table (it needs to be contains as there may not be exact matches):

Field1	Field2	Field3	Field4	Field5	Field6	COUNT
AAA	AAA	DIF	VMX	IRU	BBB	3
VCX	KEM	CCC	BBB	AAA	AAA	4
EIE	OFP	MCN	UVN	DDK	JDN	0
…	…	…	…	…	…	…

Hope someone can help. There are more than 6 fields so a bunch of 'OR's will not work. I prefer the solution to be in SAS language, not SQL. Thanks!

[ballardw]

How many values are in the look up table?

How volatile are the values or how often do the values in the look up table change?

[PeterClemmensen · Accepted solution]

Here is one way.

data have;
input (Field1-Field6)(:$10.);
datalines;
AAA AAA DIF VMX IRU BBB
VCX KEM CCC BBB AAA AAA
EIE OFP MCN UVN DDK JDN
;

data lookup;
input Field $;
datalines;
AAA
BBB
CCC
;

data want(keep=Field1-Field6 Count);
   set have;
   array a{*} $ Field1-Field6;
   count=0;
   do i=1 to n;
      set lookup point=i nobs=n;
      do j=1 to dim(a);
         if (a[j]=Field) then count+1;
      end;
   end;
run;

[kz134]

Thanks. This worked. If we remove (keep=Field1-Field6 Count), we get a column called Field in the want table. And the value is CCC. For learning purposes, what does this mean?

[PeterClemmensen]

The Field variable is from the lookup data set. Try running this code. Might clear up the logic 

data want;
   set have;
   array a{*} $ Field1-Field6;
   count=0;
   do i=1 to n;
      set lookup point=i nobs=n;
      do j=1 to dim(a);
         if (a[j]=Field) then count+1;
      end;
      output;
   end;
run;

[andreas_lds]

Variable field contains the last value in the lookup dataset.

[Ksharp]

data have;
input (Field1-Field6)(:$10.);
datalines;
AAA AAA DIF VMX IRU BBB
VCX KEM CCC BBB AAA AAA
EIE OFP MCN UVN DDK JDN
;

data lookup;
input Field $;
datalines;
AAA
BBB
CCC
;

data want;
 if _n_=1 then do;
  if 0 then set lookup;
  declare hash h(dataset:'lookup');
  h.definekey('field');
  h.definedone();
 end;
set have;
array x{*} $ Field1-Field6 ;
count=0;
do i=1 to dim(x);
  field=x{i};
  if h.check()=0 then count+1;
end;
drop i;
run;