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!
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;
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?
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;
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;
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;
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 25. 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.