DATA Step, Macro, Functions and more

SAS Merge logic

Reply
Contributor
Posts: 27

SAS Merge logic

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.

 

 

 

 

Super User
Posts: 5,079

Re: SAS Merge logic

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.

PROC Star
Posts: 7,360

Re: SAS Merge logic

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

 

Contributor
Posts: 27

Re: SAS Merge logic

Thanks Art. But the data is already present in the tables which is why I need to find a way around it.
Ask a Question
Discussion stats
  • 3 replies
  • 107 views
  • 1 like
  • 3 in conversation