topic Re: Fill a column with values from other columns only if they match and are not null in SAS Programming

Fill a column with values from other columns only if they match and are not null

bouz22 — Wed, 24 Jan 2018 12:43:17 GMT

Hello,

I have a table with columns that should contain the same data acquired from other tables, with this format:

var1	var2	var3	var4	output
car	car			car
car	truck	car
truck	truck	truck		truck
			car	car

In this example var1 to var4 contain the same string information from different sources, now I want to create the output column to the right that contains the value if it's the same for var1 to var4 only and ignores columns where the value is missing. If the values in var1 to var4 are inconsistent like in the second row it should leave the output variable empty.

I can think of a way using SQL and CASE but it would require writing out all the combinations, with the risk of missing some. Is there a quicker way to compare the vales in different columns but ignoring empty cells?

Many thanks!

Re: Fill a column with values from other columns only if they match and are not null

Kurt_Bremser — Wed, 24 Jan 2018 13:08:18 GMT

data want;
set have;
array vars{*} var:;
var_out = '___                          ';
/* use enough blanks to accomodate the maximum length of your vars */
do i = 1 to dim(vars);
  if vars{i} ne ''
  then do;
    if var_out = '___' then var_out = vars{i};
    else if vars{i} ne var_out then var_out = '';
  end;
end;
drop i;
run;

Re: Fill a column with values from other columns only if they match and are not null

bouz22 — Wed, 24 Jan 2018 13:34:32 GMT

Thank you for the quick reply. it works! This is something I would have never come up with...

Many thanks, I was stuck on this already for quite a while.