topic Understanding joins in SAS Programming

Understanding joins

AJ_Brien — Sat, 07 Sep 2019 05:58:01 GMT

Hello,

I'm trying to get a certain output through a join.

Code for creating Input datasets:

data rank;
infile datalines; 
input rmg $4. acc;
datalines; 
123	1
124	2
125	3
126	4
127	5
; 
run;

data rank1;
infile datalines; 
input rmg $4. acc;
datalines; 
123	1
124	2
125	3
123	7
124	8
125	9
136	10
138	11
; 
run;

What I'm looking to do is get all the data from table rank and get only that data from table rank1 where rank.rmg=rank1.rmg but rank.acc is not in rank1.acc to avoid duplication. So for eg, for rmg 123 in rank, the code should include 123 from rank, but in addition should also pull 123 from rank1 where acc is not 1.

So this is the output that I'm looking to get:

rmg	acc
123	1
123	7
124	2
124	8
125	3
125	9
126	4
127	5

When I try this code:

proc sql;
create table new as
select rank.*, rank1.rmg, rank1.acc from rank left join rank1
on rank.rmg=rank1.rmg
where rank1.acc not in (select distinct acc from rank);
quit;

I get this output:

rmg	acc
123	1
123	1
124	2
124	2
125	3
125	3
126	4
127	5

Then when I try this code:

proc sql;
create table new as
select rank.*, rank1.rmg as rmg1, rank1.acc as acc1 from rank left join rank1
on rank.rmg=rank1.rmg
where rank1.acc not in (select distinct acc from rank);
quit;

I get this output:

rmg	acc	rmg1	acc1
123	1	123	7
124	2	124	8
125	3	125	9
126	4
127	5

What should I be changing to get the desired output? Appreciate any suggestions.

Thank you!

Re: Understanding joins

hashman — Sat, 07 Sep 2019 08:51:12 GMT

@AJ_Brien:

Methinks that judging from your input/output data logic, what you're looking for is:

data rank ;                                                                                                                                                                                                                                                     
  input rmg :$3. acc ;                                                                                                                                                                                                                                          
  cards ;                                                                                                                                                                                                                                                       
123  1                                                                                                                                                                                                                                                          
124  2                                                                                                                                                                                                                                                          
125  3                                                                                                                                                                                                                                                          
126  4                                                                                                                                                                                                                                                          
127  5                                                                                                                                                                                                                                                          
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data rank1;                                                                                                                                                                                                                                                     
  input rmg :$3. acc ;                                                                                                                                                                                                                                          
  cards ;                                                                                                                                                                                                                                                       
123   1                                                                                                                                                                                                                                                         
124   2                                                                                                                                                                                                                                                         
125   3                                                                                                                                                                                                                                                         
123   7                                                                                                                                                                                                                                                         
124   8                                                                                                                                                                                                                                                         
125   9                                                                                                                                                                                                                                                         
136  10                                                                                                                                                                                                                                                         
138  11                                                                                                                                                                                                                                                         
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
proc sql ;                                                                                                                                                                                                                                                      
  create table want as                                                                                                                                                                                                                                          
  select * from rank                                                                                                                                                                                                                                            
  union                                                                                                                                                                                                                                                         
  select * from rank1 where rmg in (select rmg from rank)                                                                                                                                                                                                       
  ;                                                                                                                                                                                                                                                             
quit ;

UNION (without ALL) unduplicates the rows with the same RMG and ACC.

Kind regards

Paul D.

Re: Understanding joins

AJ_Brien — Sat, 07 Sep 2019 22:47:27 GMT

Thank you for your reply.

The only drawback I see with this approach is that is the moment I add additional columns in the 1st dataset (which I probably do need to do), it gives the error: Corresponding select-list expressions are not compatible, that's the reason I was trying to go with left join.

Re: Understanding joins

SASKiwi — Sat, 07 Sep 2019 23:14:52 GMT

Try OUTER UNION CORRESPONDING instead of UNION in @hashman 's example then.

Re: Understanding joins

AJ_Brien — Sun, 08 Sep 2019 00:46:48 GMT

thank you for your reply, but outer union corresponding would create additional columns for me.

So to recap, I made slight changes to my input dataset to accomodate more columns, and change their names too:

data rank;/*base*/
infile datalines;
input check $2. rmgid $4. accno blah $2.;
datalines;
y 123 1 y
n 124 2 n
y 125 3 y
n 126 4 n
y 127 5 y
;
run;

data rank1;
infile datalines;
input rmg $4. acc;
datalines;
123 1
124 2
125 3
123 7
124 8
125 9
136 10
138 11
;
run;

and the output I'm trying for is:

check	rmgid	accno	blah
Y	123	1	Y
N	124	2	N
Y	125	3	Y
N	126	4	N
Y	127	5	Y
	123	7
	124	8
	125	9

Basically trying to union them such that I get all the data from table rank and get only that data from table rank1 where rank.rmgid=rank1.rmg but rank.accno is not in rank1.acc to avoid duplication.

Re: Understanding joins

SASKiwi — Sun, 08 Sep 2019 01:04:10 GMT

Does SELECT DISTINCT instead of just SELECT give you the result you want?

Re: Understanding joins

AJ_Brien — Sun, 08 Sep 2019 01:12:40 GMT

I can't use distinct in the select statements because the rmgs are associated with different acc and I need to include them all.
The issue here is that I need the result to look kind of like it does for union (rows added below, no additional columns created), but the concern is that the 1st table contains differennt columns than the 2nd one, so simple union isn't working.

Re: Understanding joins

AJ_Brien — Sun, 08 Sep 2019 01:47:53 GMT

I tried another way: this might get me closer to where I want to be, sadly one of the queries for creating table temp1 gives me a blank output for some reason!?! Appreciate any help I can get with this, I've tried a number of ways with this query, but no result so far! This seemed easy initially, but feels tricky now

proc sql ;
create table temp as
select rmgid, accno from rank;

create table temp1 as /*this gives me a blank output, not sure why*/
select rmg as rmgid,acc as accno from rank1
where rmg in (select rmgid from rank)
and acc not in (select distinct accno from rank);

insert into temp
select rmgid, accno from temp1;
quit;

proc sql;
create table want as
select *, temp.rmgid,temp.accno
from rank full join temp
on rank.accno=temp.accno;
quit;

Re: Understanding joins

SASKiwi — Sun, 08 Sep 2019 02:34:43 GMT

Cheating.....

proc sql ;                                                                                                                                                                                                                                                      
  create table want as                                                                                                                                                                                                                                          
  select * from rank
  union  
  select  ' ' as check
         ,rmgid
         ,accno
         ,' ' as blah
  from rank1
  where rmgid in (select rmgid from rank) 
  and accno not in (select accno from rank) 
  order by accno 
  ;                                                                                                                                                                                                                                                             
quit ;

Re: Understanding joins

AJ_Brien — Sun, 08 Sep 2019 03:09:55 GMT

Strangely, that's giving me this result:
check rmgid accno blah
Y 123 1 Y
N 124 2 N
Y 125 3 Y
N 126 4 N
Y 127 5 Y

Feels like the where conditions are not working for some reason.
But I have another question, and you'll hate me for saying this, but is this scalable? Meaning in this scenario I luckily happen to have just 4 columns in rank, but what if I have 100 columns or so. Trying to understand the best way to perform this logic and become better. Thank you for your help so far!

Re: Understanding joins

SASKiwi — Sun, 08 Sep 2019 03:22:13 GMT

Works fine for me with this complete program:

data rank;
infile datalines;
input check $2. rmgid $4. accno blah $2.;
datalines;
y 123 1 y
n 124 2 n
y 125 3 y
n 126 4 n
y 127 5 y
;
run;                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                
data rank1;
infile datalines;
input rmgid $4. accno;
datalines;
123 1
124 2
125 3
123 7
124 8
125 9
136 10
138 11
;
run;

proc sql ;                                                                                                                                                                                                                                                      
  create table want as                                                                                                                                                                                                                                          
  select * from rank
  union  
  select  ' ' as check
         ,rmgid
         ,accno
         ,' ' as blah
  from rank1
  where rmgid in (select rmgid from rank) 
  and accno not in (select accno from rank) 
  order by accno 
  ;                                                                                                                                                                                                                                                             
quit ;

If you want to scale this solution then I'd probably start looking at using DATA steps as they are a whole lot more flexible for stacking tables.

Re: Understanding joins

hashman — Sun, 08 Sep 2019 03:36:24 GMT

@SASKiwi:

>If you want to scale this solution then I'd probably start looking at using DATA steps as they are a whole lot more flexible for stacking tables.<

You snatched it off the tip of my proverbial online tongue ;).

And thank you for joining (no pun intended) the thread with your astute responses.

Kind regards

Paul D.

Re: Understanding joins

SASKiwi — Sun, 08 Sep 2019 03:49:14 GMT

@hashman - Thanks for providing the original UNION solution - I just started playing with it...

Re: Understanding joins

AJ_Brien — Sun, 08 Sep 2019 18:19:32 GMT

thank you so much for all your help @SASKiwi @hashman! I get to learn so much from this community 🙂