DATA Step, Macro, Functions and more

Help with merging and duplicates

Reply
Occasional Contributor
Posts: 14

Help with merging and duplicates

[ Edited ]

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
;

 

Occasional Contributor
Posts: 16

Re: Help with merging and duplicates

Posted in reply to serrld113

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;

SAS Super FREQ
Posts: 9,260

Re: Help with merging and duplicates

Posted in reply to Larrihoover

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

Occasional Contributor
Posts: 16

Re: Help with merging and duplicates

Posted in reply to Cynthia_sas

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;

Super User
Posts: 13,084

Re: Help with merging and duplicates

Posted in reply to serrld113

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.

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 100 views
  • 0 likes
  • 4 in conversation