If the order of both datasets is already correct, then a MERGE without BY can also do that side-by-side lineup. ... View more

Thank you for the quick response. I tried a regular data step sorting by place_of_employment that messed things up because values for the other variables did not come through.   Thank you for this approach.   wklierman ... View more

@wlierman wrote: There are 165,000 contacts and most have one CCO that they are assigned to. There are a few (say 100) that have 2 CCOs because as I mentioned because a zipcode will be split between the CCOs. So of the 165K about 135K have a CCO - zipcode or county is missing. This is survey data so much of the data whether REALD or demographic will have some missing or incomplete responses. So my question is if I use the Coalesce statement in the query do I code a subsetting "if"condition in a data step before the query? I want a condition that catches the 100 or so obs (contacts) that happen to have two CCOs. Thank you for your help. Still have described one or more rules that tell us which "contact" might need the 2 CCO. Your example data does not show Zipcodes at all. And now you need to define exactly what is meant by "Zipcode will be split between CCO". Especially since you say Zipcdoes may be missing for up to 75% of the records. That tends to make it a poor decision variable.   Coalesce will never return two values. It returns the first non-missing in a list of values. So I don't get why you keep saying coalesce. The CATX function will do that. Have you tested it? You would need to provide more complete data as to why it is not appropriate. ... View more

Thank you. My thoughts precisely.   ... View more

Thank you for the help.  I have a dataset that merged without dups so I'm sending that forward.  I am going to work on this smaller but dup containing dataset.   Thanks again.   wklierman ... View more

Thank you for the link to the paper - looks to be helpful. Also I appreciate your iterative approach to exploring this challenge. That iterative approach is very closely related to the response from SASkiwi.   Thank you for your help. I really appreciate it.   wklierman ... View more

Your code approach also produces the output that I am needing.   I would also post your email as a solution along with the solution from Tom.   But I don't know how to submit a second solution.   Thanks for your help.   wlierman ... View more

Your code approach also produces the output that I am needing.   I would also post your email as a solution along with the solution from Tom.   But I don't know how to submit a second solution.   Thanks for your help.   wlierman ... View more

Not sure why you want to attach WORD file to show a few lines of text. The counts you show with 20% missing is in-line with the data generation method you showed in your original posting.     You need to attach numeric formats to numeric variables and character formats to character variables.   If you are asking to test how many of you character values can be converted into numbers that is a totally different test and would need totally different kind of code.  Ask a new question about that.   Note that the code you posted to generate test data only created CHARACTER variables.  If you want to know how to generate some test data with numeric missing data open a new question. ... View more

Should padding a white blank : Select catx('', --> Select catx(' ', ... View more

Thank you for the code. I will do that. wlierman ... View more

You can make fake data like I did in my posts though or by spending a few minutes in Excel making similar data.   @wlierman wrote: Thank you. The data is from OPERA and not deidentified. So I have to maintain the rules that have been formulated.   ... View more

Thank you.  It will be very helpful.   wklierman ... View more