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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.