topic Re: Full Outer Join in a loop in SAS Programming

Full Outer Join in a loop

vkabdwal — Fri, 10 Sep 2021 14:39:11 GMT

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?

Re: Full Outer Join in a loop

PaigeMiller — Fri, 10 Sep 2021 15:24:01 GMT

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.

Re: Full Outer Join in a loop

Sajid01 — Fri, 10 Sep 2021 16:33:33 GMT

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.

Re: Full Outer Join in a loop

vkabdwal — Fri, 10 Sep 2021 18:39:19 GMT

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.

Re: Full Outer Join in a loop

vkabdwal — Fri, 10 Sep 2021 18:40:07 GMT

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.

Re: Full Outer Join in a loop

Sajid01 — Fri, 10 Sep 2021 18:46:34 GMT

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.

Re: Full Outer Join in a loop

Patrick — Sat, 11 Sep 2021 02:52:42 GMT

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.

Re: Full Outer Join in a loop

Sajid01 — Sat, 11 Sep 2021 03:17:41 GMT

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