topic Re: Making all records in a spreadsheet unique and removing duplicates from other speadsheets in SAS Programming

Making all records in a spreadsheet unique and removing duplicates from other speadsheets

Ricardo96 — Tue, 02 Nov 2021 13:10:41 GMT

Good Afternoon Everyone,

My problem is as follows:

i have a workbook with no unique identifiers and data that appears to be duplicates but the cleint requested that the information be kept as is and that the information we see within the spreadsheet is not a duplicate.

I have assigned unique identifiers to the records using COUNT() but where the problem comes in is that when we receive new files from the client, The new files could have the exact same information as the previous workbook with one or two new records. How do i keep only the new records from the new file so that i can append it to my existing dataset and remove the duplicates from the new data because they are duplicates from the previous workbook and i do not want that in my dataset while still assigning my unique identifiers to the records.

Please find attached an example of what the Spreadsheet looks like:

So i want to keep both instances of handy inc and assign unique identifiers for both records.

but if these same records appear in a newly sent spreadhseet by a client that those rows be excluded and only the new records be kept for appending.

Thank you in advance for all assistance provided

Re: Making all records in a spreadsheet unique and removing duplicates from other speadsheets

Kurt_Bremser — Tue, 02 Nov 2021 13:21:39 GMT

UNION in SQL checks for duplicates on its own:

proc sql;
create table new_dataset as
  select * from old_dataset
  union
  select * from update_dataset
;
quit;

Note that this involves an internal sort over all variables, so it can become very resource-intensive with larger data.

OTOH, spreadsheets from Excel are not "large" as they max out at ~ 1 million rows.

Re: Making all records in a spreadsheet unique and removing duplicates from other speadsheets

Ricardo96 — Wed, 03 Nov 2021 15:55:23 GMT

@Kurt_Bremser Thank you.

Re: Making all records in a spreadsheet unique and removing duplicates from other speadsheets

Ricardo96 — Wed, 29 Jun 2022 10:01:34 GMT

Thank you Very much for the assistance on this problem...huge help.