Full Outer Join in a loop

[vkabdwal]

I have a macro with a loop inside it which creates two tables per iteration.

First table has the count of new subscribers on a yearly basis:

data table1;

input fy sub_new;

datalines;

2010  5

2012  6

2013  3

2014  4

2017  2

run;

The second table has the count of subscribers who leave on a yearly basis: 

data table2;

input fy sub_left;

datalines;

2012  5

2013  1

2017  4

run;

Now I combine both the tables table1 and table2 to create a final table with FY  sub_new  sub_left  total_year (sub_new + sub_left) as the columns. Now loop runs once for every category in my dataset.

Problem:

For some categories, I don't have any data in the table2. So  a blank row is passed to the full outer join in the loop iteration and it gives an error. The entire macro stops working.

Is there a way to ensure that the full outer join still works even when a null table (with no columns) is passed to it?

Idea is to get four columns: One with FY, other with sub_new(table1), third with sub_left(table2) and fourth is sum of col2 and col3 (say Total). Matching is done on FY basis.

If outer join won't work for null tables, what else can i use in the loop?

[PaigeMiller · Accepted solution]

Is there a way to ensure that the full outer join still works even when a null table (with no columns) is passed to it?

Yes, you could check the existence of the second table and the number of rows in the second table, store that information in SAS macro variables, and then something like this

%if &table_exists and &num_rows>0 %then %do;
/* SQL Outer Join */
%end;

Or maybe its simpler than that, but you didn't show us your join code and you didn't show us the errors you are getting. Please show us by providing the log so we can see both code and errors in the log.

[Sajid01]

Hello @vkabdwal 

You have this issue.

For some categories, I don't have any data in the table2. So  a blank row is passed to the full outer join in the loop iteration and it gives an error. The entire macro stops working.

The following code will solve the issue

data table1;
input fy sub_new;
datalines;
2010  5
2012  6
2013  3
2014  4
2017  2
;
run;
data table2;
input fy sub_left;
datalines;
2012  5
2013  1
2017  4
;
run;

proc sql;
create table final_table as
select a.*,b.* , (coalesce(sub_left,0)+coalesce(sub_new,0)) as Total_year
from table1 a
full outer join table2 b  on 
a.fy=b.fy;
quit;

The output dataset (final_table) will be like this.

Please let me know if you have questions.

[vkabdwal]

Updated:
Problem:

For some categories, I don't have any data in the table2. So a blank table is passed to the full outer join in the loop iteration and it gives an error. The entire macro stops working. Not a blank row in 2nd table but the entire table is null.

[vkabdwal]

Correction:
Original:
Problem:

For some categories, I don't have any data in the table2. So a blank row is passed to the full outer join in the loop iteration and it gives an error. The entire macro stops working.

Updated:
Problem:

For some categories, I don't have any data in the table2. So a blank table is passed to the full outer join in the loop iteration and it gives an error. The entire macro stops working.

[Sajid01]

Before converting to a macro one needs to test the code.
So please test the code I gave with the your data.

For any category if there is no data in table2, the final_table will have data from table1 and missing values in the corresponding column of table2. In this case the Total_year will have the value from table1.

Based on the information in your post I don't foresee any error occurring
If you do have an error please post the data for the category from both tables and the log.

[Patrick]

The SQL @Sajid01 posted will also work if one of the tables has zero rows. Just test it out. It works.

You're talking about some macro falling over so you would now need to share and explain this macro logic to us. ...and may be also explain why you need a macro at all and why this can't be done with Base SAS/SQL only.

[Sajid01]

Hello @vkabdwal 

There should be no error  as long as table2 exists. The code would obviously fail if table2 does not exist.

Please see the code below.

data table1;
input fy sub_new;
datalines;
2010  5
2012  6
2013  3
2014  4
2017  2
;
run;

proc sql;
create table table2(Rename=(sub_new=sub_left)) like table1;
quit;

proc sql;
create table final_table as
select a.*,b.* , (coalesce(sub_left,0)+coalesce(sub_new,0)) as Total_year
from table1 a
full outer join table2 b  on 
a.fy=b.fy;
quit;

The output will be as follows