07-20-2017 01:51 PM
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.
07-20-2017 02:52 PM
You'll need to unwind all the comma-separated entries:
do i=1 to countw(var1, ',');
var_want = scan(var1, i, ',');
Then there are many ways to find mismatches. Here's one:
create table mismatch as select var_want from check_these
where var_want not in (select distinct var_1 from a);
The code is untested, so minor tweaking might be necessary.
07-20-2017 03:02 PM
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