BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bouz22
Calcite | Level 5

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!

 


 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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;

 

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User
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;

 

bouz22
Calcite | Level 5

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.

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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