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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.