DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

Hello,

 

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

 

var1var2var3var4output
carcar  car
cartruckcar  
trucktrucktruck truck
   carcar

 

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!

 


 


Accepted Solutions
Solution
‎01-24-2018 08:34 AM
Super User
Posts: 9,593

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

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎01-24-2018 08:34 AM
Super User
Posts: 9,593

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

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 2

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

Posted in reply to KurtBremser

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.

 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 81 views
  • 0 likes
  • 2 in conversation