topic Re: Help with merging and duplicates in SAS Programming

Help with merging and duplicates

serrld113 — Thu, 10 May 2018 15:37:27 GMT

How do I add my flags to my table1 id's? Without adding or subtracting from my existing table1 id's?

I have 3 tables.

table1 has thousands of id's, some are duplicate and I need to keep them all (even the dups). On my other table I have many more id's and a 1 or 0 flag, same for the third table but it is a different flag (but still 1 or 0).

edit: table2 and table3 have several duplicates...so if a record has a 1 flag (or several) then on my join table I'd like them to have a 1 flag. But if the there several duplicates and they all have a 0 flag and no 1 flag then my new table should also have a 0 flag.

I want to add another 2 columns to table1 where I keep all the original id's (even the dups) and the new column contains the flags for other two tables by id.

my problem is when I use this code, it adds more dups, but if I use distinct then I drop the dups that I do need (the ones from table1)

proc sql;
create table join as 
select a.ed_id, score1, score2
from table1 a
left join table2 b
on a.ed_id= b.ed_id
left join table3 c
on a.ed_id= c.ed_id
;

Re: Help with merging and duplicates

Larrihoover — Thu, 10 May 2018 15:46:35 GMT

The simplest I can think of is probably this using proc sql:

proc sql;
drop table table1;
create table table1(
edid char(20)
);
insert into table1
values ('dup1')
values ('dup2')
values ('dup1')
values ('dup1')
values ('dup3')
;
create table table1 as
select *, monotonic() as row
from table1
order by edid;
quit;

proc sql; create table test as
select a.edid, a.row
,case when a.row = b.m_row then 1 else 0 end as dup_flag
from table1 a
left join (select distinct edid, min(row) as m_row from table1) b
on a.edid = b.edid
order by a.edid
;
quit;

Re: Help with merging and duplicates

ballardw — Thu, 10 May 2018 15:51:51 GMT

Show some example input data for each data set and the desired final output. Best is if each of the sets is presented as data step code.

Re: Help with merging and duplicates

Cynthia_sas — Thu, 10 May 2018 18:03:49 GMT

Hi:
Just a note about using MONOTONIC -- there have been previous postings about this -- there's a Tech Support note http://support.sas.com/techsup/notes/v8/15/138.html that says:
"The MONOTONIC() function is not supported in PROC SQL. Using the MONOTONIC() function in PROC SQL can cause missing or non-sequential values to be returned."

This might make the suggestion produce unreliable results.

Cynthia

Re: Help with merging and duplicates

Larrihoover — Thu, 10 May 2018 20:06:40 GMT

This will fix flags for table 1. From reading your question again, I am not sure for table 2 and 3 how the data will join without seeing it or if I am understanding the question correctly.

proc sql;
drop table main;
create table main(
edid char(20)
);
insert into main
values ('dup1')
values ('dup2')
values ('dup1')
values ('dup1')
values ('dup3')
;
quit;
proc sort data=main; by edid; run;

data table1;
set main;
count + 1;
by edid;
if first.edid then do;
count = 1;
flag_t1=1;
end;
else flag_t1 = 0;
run;