topic Re: Computed column in SAS EG query builder to merge null field from two columns into one in SAS Enterprise Guide

Computed column in SAS EG query builder to merge null field from two columns into one

WorkUser — Tue, 31 Aug 2021 12:26:36 GMT

Can I do a computed column in SAS EG query builder to create a new column that merges two different columns into one. Below is an example of what I’m talking about:

Item Number	Company	Parent Company
1234	ABC LLC	ABC Inc
5678	123 LLC	123 Inc
9123		XYZ Inc
8765	QRT LLC

Create a new column to show:

Item Number	New Column
1234	ABC Inc
5678	123 Inc
9123	XYZ Inc
8765	QRT LLC

Thanks,

Re: Computed column in SAS EG query builder to merge null field from two columns into one

ballardw — Tue, 31 Aug 2021 14:52:10 GMT

Don't know how query builder might use the COALESCEC function but that appears to be what you want. Look for how to build an expression.

The generated code would look something like:

Proc sql;
   create table new as
   select itemnumber, coalescec(company,parentcompany) as newcolumn
   from have;
run;

The Coalescec function for character values, and Coalesce for numeric, returns the first value encountered in the list of values provided in the function call in order from left to right. So when Company is populated that is the result, when missing then Parentcompany is the result. If both are missing the result will be missing.

Re: Computed column in SAS EG query builder to merge null field from two columns into one

WorkUser — Wed, 01 Sep 2021 12:31:38 GMT

Thanks but I think I figured it out with a case statement

case

when not missing(parentcompany) then parentcompany
else company

end