I am working on a linkage and I have two datasets with numerous missing values that are coded as all 9s in both datasets. I am trying to make sure that when the linkage codes for "Var_A" that it does not label matches for those that are all 9s. I want the other variables to match where applicable without matching those missing values. The example code is below. How might I edit that to accomplish this task?
proc sql;
create table Set_A as
select
a.Var_A, b.Var_A as Var_A,
a.Var_B, b.Var_B as Var_B,
a.Var_C, b.Var_C as Var_C
from File1 a, File2 b
where (complev(a.Var_B, b.Var_B, 'ILN') lt 1) and /*Perfect match on Var_B*/
(
( b.Var_A =* a.Var_A and b.Var_B =* a.Var_B)
and ((a.Var_C=b.Var_C))
);
quit;
Some additional information that might help:
Are all of the variables in question character, numeric or mixed?
You might post a few rows of data and what you want the outcome to look like.
I'm assuming that in this code snippet you missed adding something to the B variable names, otherwise you're likely to have unexpected results:
a.Var_A, b.Var_A as Var_A, /* <=would try to create two variables named Var_a, did you mean b.Var_A as Var_A_b or similar?*/
a.Var_B, b.Var_B as Var_B,
a.Var_C, b.Var_C as Var_C
The variable names are hypothetical. The actual code is set up differently. The variables are mixed. I just need to know how to add in a clause that prevents matching on a value of '999999999' because that indicates a missing value and both datasets are coded the same way for the missing values. I have to use this variable as a match variable, but all of the missings will mess up my results if I can't figure out how to avoid matching on teh missing values.
Without some data to work with it is hard to test any code that might work. If you can provide a few rows of input data in the form of a datastep and what the desired output might be it really helps to answer your question. Include some cases with each of the types of issues you expect to incounter such as no missing, one or two missing, all missing.
And the approach could well depend upon whether your current values are numeric or character either by adding or reducing a step in the solution.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.