topic Re: SAS Linkage in SAS Programming

SAS Linkage

chadwae — Tue, 09 Feb 2016 20:36:51 GMT

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;

Re: SAS Linkage

ballardw — Tue, 09 Feb 2016 21:14:26 GMT

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

Re: SAS Linkage

chadwae — Tue, 09 Feb 2016 21:31:55 GMT

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.

Re: SAS Linkage

ballardw — Tue, 09 Feb 2016 23:28:28 GMT

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.

Re: SAS Linkage

chadwae — Tue, 09 Feb 2016 23:42:17 GMT

I unfortunately can't include any data. The information I'm working with is confidential which is why that's not the exact code. I just need an idea of some syntax that might work. Just something to point me in the direction of adding an exception so it won't try and match on the unknowns which is '999999999'. I want to match on everything except those.

Re: SAS Linkage

chadwae — Tue, 09 Feb 2016 23:42:51 GMT

The variables are mostly character. I know how to do conversions if I need to.