topic Re: SAS Merge logic in SAS Programming

SAS Merge logic

adityaa9z — Thu, 20 Jul 2017 17:51:02 GMT

Hi all.

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

Below is the Dataset A

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.

Re: SAS Merge logic

Astounding — Thu, 20 Jul 2017 18:52:18 GMT

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.

Re: SAS Merge logic

art297 — Thu, 20 Jul 2017 19:02:14 GMT

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

Re: SAS Merge logic

adityaa9z — Thu, 20 Jul 2017 19:19:16 GMT

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