topic Re: Field comparison in SAS Programming

Field comparison

Sir_Lancelot — Mon, 30 Jul 2018 11:48:49 GMT

I am trying to compare two fields and output the difference. Please see below example:

field_1	field_2	results
003,004,006	003,004	006
002,003,005	002,005	003
003,004	003,004,006	006
002,005	002,003,005	003

Thanks in advance

Re: Field comparison

RW9 — Mon, 30 Jul 2018 12:12:39 GMT

Will it always be like that, i.e. field_2 as a subet of field_1? If so:

results=strip(tranwrd(field_1,field_2,""));

If not then you will need to loop through then:

data want;
  set have;
  do i=1 to countw(field_2,",");
    if findw(field_1,scan(field_2,i,","),",") then results=scan(field_2,i,",");
  end;
run;

Note that this only assumes one difference, other wise you will need:

    if findw(field_1,scan(field_2,i,","),",") then results=catx(",",results,scan(field_2,i,","));

Re: Field comparison

Astounding — Mon, 30 Jul 2018 13:23:09 GMT

Do I need a cup of coffee, or should the FINDW result be compared to 0?

Re: Field comparison

RW9 — Mon, 30 Jul 2018 13:25:04 GMT

I would have tea myself. But yes, I was going with the reverse mistakenly. You can either compare with zero or not() it:

data want;
  set have;
  do i=1 to countw(field_2,",");
    if not(findw(field_1,scan(field_2,i,","),",")) then results=scan(field_2,i,",");
  end;
run;

Re: Field comparison

Ksharp — Mon, 30 Jul 2018 13:30:44 GMT

data have;
input field_1 : $20.	field_2	:$20.;
a=translate(field_1,'|',',');
b=translate(field_2,'|',',');
x1=prxchange(cats('s/',b,'//'),-1,translate(field_1,' ',','));
x2=prxchange(cats('s/',a,'//'),-1,translate(field_2,' ',','));

want=catx(' ',x1,x2);
drop a b x1 x2;
cards;
003,004,006    003,004
002,003,005     002,005
003,004        003,004,006
002,005      002,003,005
;
run;

proc print noobs;run;

Re: Field comparison

ballardw — Mon, 30 Jul 2018 14:24:30 GMT

@Sir_Lancelot wrote:

I am trying to compare two fields and output the difference. Please see below example:

field_1 field_2 results

003,004,006 003,004 006

002,003,005 002,005 003

003,004 003,004,006 006

002,005 002,003,005 003

Thanks in advance

And perhaps yet another example why having multiple values in a single field such as "003, 004, 006" is a poor idea in general.