If you ever need to remove duplicate rows from a SAS dataset, here’s an approach I use quite often.<\/p>\n
Let’s assume it’s in the following format: Which should give you the following: Now that the data is in order, we can remove the duplicates, by only ever keeping the first entry which matches our unique ID. –<\/span><\/p>\n – This approach has 3 facets:<\/p>\n –<\/span> First.variable:\u00a0<\/strong>During execution, SAS will iterate through each row of my_data_set and adding it to a new dataset (which it will eventually overwrite my_data_set with). During each iteration, if it hits a row with the first use of an ID value (for example, 123), it will set first.id to true. On the next run, because it’s already seen the value 123 before, first.id is set to false. This gives us a handy flag which will only ever be toggled on unique rows.<\/p>\n Funny Statement Stuff:\u00a0<\/strong>So how do we flag to SAS that when this value is set to true, to keep the row?\u00a0When evaluating a data row if at any point we make any floating statement (i.e. not assigning a variable, or in an if or do loop) which evaluates to false, SAS will take that as a sign that it shouldn’t output that row i.e. in this case, it shouldn’t keep it.<\/p>\n So in simple terms, we’re saying – if you’ve seen this value before, don’t save it again.<\/p>\n","protected":false},"excerpt":{"rendered":" If you ever need to remove duplicate rows from a SAS dataset, here’s an approach I use quite often. Get your data. Let’s assume it’s in the following format: – ID Name 123 John 456 Bob 123 John Sort your\u00a0data. – \/* Step 1 – Sort data *\/ proc sort data=my_lib.my_dataset; \/* Sort by a… Read More »Removing duplicate rows in base SAS<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":""},"categories":[23,24],"tags":[70,147,151,152],"_links":{"self":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/posts\/364"}],"collection":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/comments?post=364"}],"version-history":[{"count":0,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/posts\/364\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/media?parent=364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/categories?post=364"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/tags?post=364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n–<\/span><\/p>\n\n\n
\n ID<\/strong><\/td>\n Name<\/strong><\/td>\n<\/tr>\n \n 123<\/td>\n John<\/td>\n<\/tr>\n \n 456<\/td>\n Bob<\/td>\n<\/tr>\n \n 123<\/td>\n John<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Sort your\u00a0data.
\n–<\/span><\/h2>\n\/* Step 1 - Sort data *\/<\/span>\nproc sort data=my_lib.my_dataset;\n\n \/* Sort by a field which you want to be unique, \n and which will be the same for duplicate rows *\/<\/span>\n by id; \n\nrun;<\/pre>\n
\n–<\/span><\/p>\n\n\n
\n ID<\/strong><\/td>\n Name<\/strong><\/td>\n<\/tr>\n \n 123<\/td>\n John<\/td>\n<\/tr>\n \n 123<\/td>\n John<\/td>\n<\/tr>\n \n 456<\/td>\n Bob<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n –<\/span>
\nRemove Duplicates<\/h2>\n
\n-.<\/span><\/p>\n\/* Step 2 - Get rid of duplicates *\/<\/span>\ndata my_lib.my_dataset;\n\n \/* Iterate through this dataset row by row *\/<\/span>\n set my_lib.my_dataset;\n \/* Grouping each row by the field we sorted on *\/<\/span> \n by id; \n \/* And only keep a row if it\u2019s the first *\/<\/span>\n if first.id; \n\nrun;<\/pre>\n\n\n
\n ID<\/strong><\/td>\n Name<\/strong><\/td>\n<\/tr>\n \n 123<\/td>\n John<\/td>\n<\/tr>\n \n 456<\/td>\n Bob<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n<\/span>Tadaa!<\/p>\nWhat happened there?<\/h2>\n
\n
\nGrouping:\u00a0<\/strong>So we effectively rearranged our data so that all identical IDs were grouped together. Given that the rows are identical and you only want to keep one of them, we choose to keep the first of each group.<\/p>\n