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!
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.