@bknitch wrote: So for that I'm only concerned where if Missing is higher in the hierarchy. Meaning, if an A3 was populated in 2019 and not populated in 2020 but an A2 was populated in 2020 and not in 2019 I would exclude this/ or not look at this record. Hope that makes sense... Sorry, no I still don't understand. What does "exclude" mean in the context of your earlier description of how to assign the "Missing" "Found" "NoMatch" to each cell?   But I think we have gone far enough down this path. If the code provided so far meets your need, fine, problem solved. If not, I am asking you to re-write the requirements from scratch, to address all of these issues, so that the explanation is clear and in one description of the logic, so we don't have to scroll up and down and re-read earlier comments to put it all together and understand. ... View more

Check out this article, it may be of some use to you. https://www.lexjansen.com/nesug/nesug06/cc/cc12.pdf ... View more

@smantha Thank you for this, it did produce results I was in favor of. I actually found my issue when trying the code out again.  proc sql; create table b_want as select memid, clndr_cy_mo_num, c_id from TEST group by 1,2 having count (clndr_cy_mo_num) >1; quit; ... View more

can you post the expected output like ballardw asked. There are few inconsistencies in the way you code 1. Length statement as you write it is ineffective length statement should always be before a set statement otherwise the length of the variables from the input dataset is considered as length and for other char variables it is set to a default of 8. 2. Are you trying to retain first pid15 and last pid16 ? ... View more

@bknitch wrote: Hi @ed_sas_member  thank you for the suggestion, unfortunately a transpose isn't plausible with the data set i'm working with, this is only a snippet of the fields that are contained within this data set. There are other changing variables per previous_plan_id that cause the transpose not to function correctly. I'm looking for more of a rollup or retain function where desc= 'Consolidated' and then concatenating the fields.With this step i would have to build several transposed tables excluding several fields and then merging.  KEEP on the variables needed for Proc transpose. The Data set option KEEP (or drop) is always available to reduce a data set: Proc transpose data=have (keep= <list variables needed in the transpose> where=(desc='Consolidated'))  <rest of transpose code>. Without rules as to how to select other variable values the result of the data would likely have to be merged back to your original. ... View more

You could perform a simple Proc Sql and join to the associated tables but it appears your identifiers are not unique to values. Meaning TYPE_ID which is your key is not unique to one FAM_ID or DES on your 3rd (T3) and 4th (T4) table so you will get a 1 to many relationship. In order for this to work you would need T3.FAM_ID = T4.FAM_ID by T3.DES = T4.DES and utilize T3.TYPE_ID = T2.TYEP_ID. Unless these values do equal each other somewhere on your table and are not included on your sample I don't think this will work.  ... View more