BookmarkSubscribeRSS Feed
serrld113
Obsidian | Level 7

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
;

 

4 REPLIES 4
Larrihoover
Obsidian | Level 7

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;

Cynthia_sas
SAS Super FREQ

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

Larrihoover
Obsidian | Level 7

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;

ballardw
Super User

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.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1425 views
  • 0 likes
  • 4 in conversation