SAS Merge logic

[adityaa9z]

Hi all.

Need a solution for Merge operation i Need to perform on a varaible.

Below is the Dataset A 

VAR_1
123
456
789
1
34
45

Below is Dataset B (Please note, some values are separated by comma )

VAR_1
123,456,345
789,2
34
67

The output should look like below.(It should look for Var_1 values from A, try and match with VAr_1 from B and if they dont match, it should output)

VAR_WANT
345
2
67

Please suggest an efficient way to do this.

[Astounding]

You'll need to unwind all the comma-separated entries:

data check_these;

set B;

do i=1 to countw(var1, ',');

   var_want = scan(var1, i, ',');

   output;

end;

keep var_want;

run;

Then there are many ways to find mismatches.  Here's one:

proc sql;

create table mismatch as select var_want from check_these

where var_want not in (select distinct var_1 from a);

quit;

The code is untested, so minor tweaking might be necessary.

[art297]

Regardless of whether you use a datastep or proc sql, easier if you separate the comma delimited entries during the input process. Here is a datastep approach:

data a;
  input VAR_1;
  cards;
123
456
789
1
34
45
;
data b;
  infile cards delimiter=',';
  input VAR_1 @@;
  cards;
123,456,345
789,2
34
67
;

proc sort data=a;
  by var_1;
run;

proc sort data=b;
  by var_1;
run;

data want;
  merge a (in=ina) b (in=inb);
  by var_1;
  if inb and not ina;
run;

Art, CEO, AnalystFinder.com

[adityaa9z]

Thanks Art. But the data is already present in the tables which is why I need to find a way around it.