BookmarkSubscribeRSS Feed
chadwae
Fluorite | Level 6

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; 

5 REPLIES 5
ballardw
Super User

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

chadwae
Fluorite | Level 6

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. 

ballardw
Super User

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.

chadwae
Fluorite | Level 6
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.
chadwae
Fluorite | Level 6
The variables are mostly character. I know how to do conversions if I need to.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1198 views
  • 0 likes
  • 2 in conversation