Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Re: If value is missing then bring in value from other table (left joi...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 05-29-2021 06:43 PM
(1738 views)

Hi! I am having the worst time of my life right now with what should be a super simple procedure. I have three tables. The first table (A) shows all the possible combinations of Group and Bucket. The second table (B) has values for random combinations of Group and Bucket, but also has another field, Type, that comes into play. And the third table (C) has Avg_Values for each combination of Bucket and Type. What I want is the fourth table (D), where it brings in all possible combinations of Group and Bucket for both types, and matches them with their value from table (B). But if (B) has no matching value, then grab the Avg_Value for that Bucket and Type from table (C).

(A)

Group | Bucket |

Red | a |

Red | b |

Red | c |

Blue | a |

Blue | b |

Blue | c |

Green | a |

Green | b |

Green | c |

(B)

Type | Group | Bucket | Value |

First | Red | a | 2 |

First | Red | b | 9 |

First | Red | c | 1 |

First | Blue | a | 3 |

First | Blue | c | 8 |

First | Green | b | 4 |

First | Green | c | 5 |

Second | Red | a | 4 |

Second | Red | c | 7 |

Second | Blue | a | 9 |

Second | Blue | b | 8 |

Second | Green | a | 10 |

Second | Green | b | 2 |

Second | Green | c | 1 |

(C)

Type | Bucket | Avg_Value |

First | a | 99 |

First | b | 89 |

First | c | 79 |

Second | a | 69 |

Second | b | 59 |

Second | c | 49 |

(D)

Type | Group | Bucket | New_Value |

First | Red | a | 2 |

First | Red | b | 9 |

First | Red | c | 79 |

First | Blue | a | 3 |

First | Blue | b | 89 |

First | Blue | c | 8 |

First | Green | a | 99 |

First | Green | b | 4 |

First | Green | c | 5 |

Second | Red | a | 4 |

Second | Red | b | 59 |

Second | Red | c | 7 |

Second | Blue | a | 9 |

Second | Blue | b | 8 |

Second | Blue | c | 49 |

Second | Green | a | 10 |

Second | Green | b | 2 |

Second | Green | c | 1 |

It really should be simple but can't for the life of me figure it out. Can anyone here help? Obviously I am in serious distress since its a Saturday before a holiday and I'm working on this lol. Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

The following code does what you want:

```
data a; /*This will be the base data set (the one that has all the cases)*/
length Type $8 Group $8 Bucket $8;
set a;
type="First";
output;
type="Second";
output;
run;
proc sql;
create table d as
select a.type ,a.group ,a.bucket ,coalesce(b.Value,c.Avg_Value) as New_Value
from a left join b on (a.type=b.type and a.group=b.group and a.bucket=b.bucket) /*In this join we take the values from b*/
left join c on (a.type=c.type and a.bucket=c.bucket) /*In this join we take the avg values from c*/
/*the coalesce function takes the first non missing value from its arguments*/
order by a.type, a.group desc, a.bucket;
quit;
```

Note: in your example you had

data set b

First | Red | c | 1 |

data set d

First | Red | c | 79 |

, I assumed it was a mistake.

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

The following code does what you want:

```
data a; /*This will be the base data set (the one that has all the cases)*/
length Type $8 Group $8 Bucket $8;
set a;
type="First";
output;
type="Second";
output;
run;
proc sql;
create table d as
select a.type ,a.group ,a.bucket ,coalesce(b.Value,c.Avg_Value) as New_Value
from a left join b on (a.type=b.type and a.group=b.group and a.bucket=b.bucket) /*In this join we take the values from b*/
left join c on (a.type=c.type and a.bucket=c.bucket) /*In this join we take the avg values from c*/
/*the coalesce function takes the first non missing value from its arguments*/
order by a.type, a.group desc, a.bucket;
quit;
```

Note: in your example you had

data set b

First | Red | c | 1 |

data set d

First | Red | c | 79 |

, I assumed it was a mistake.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Oh my god you are a beautiful person thank you so much! I knew it was super simple I just couldn't wrap my head around it for some reason. Thank you and I love you.

**Available on demand!**

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

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.