05-10-2018 11:17 AM - edited 05-10-2018 11:37 AM
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 ;
05-10-2018 11:46 AM
The simplest I can think of is probably this using proc sql:
drop table table1;
create table table1(
insert into table1
create table table1 as
select *, monotonic() as row
order by edid;
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
05-10-2018 02:03 PM
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.
05-10-2018 04:06 PM
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.
drop table main;
create table main(
insert into main
proc sort data=main; by edid; run;
count + 1;
if first.edid then do;
count = 1;
else flag_t1 = 0;