DATA Step, Macro, Functions and more

IF/Then Statement tying multiple rows together

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

IF/Then Statement tying multiple rows together

[ Edited ]

I transposed data from long to wide and now I need to compare multiple strings (up to four string vars) to determine similitude. I need to concatenate  only the unequal strings.

 

example:

ID       comment1              comment2             comment3       Comment4              RESULT

1001   This is some text   This is some text                                                           This is some text

1002   More text like this  More text like this   Not the same   More text like this   More text like this Not the same

 

Currently, only my first if-then is holding, which is "if comment1=comment2 then COMMENT=comment1". I'll spare you the rest, because it's not pretty.

 

Thanks in advance.


Accepted Solutions
Solution
‎12-18-2015 08:29 AM
Super User
Posts: 5,504

Re: IF/Then Statement tying multiple rows together

Posted in reply to UMAnalyst

SImplest would be to change the variables to eliminate duplicates:

 

array comment {4};

do I=1 to 3;

   do J=i+1 to 4;

      if comment{I} = comment{J} then comment{J}=' ';

   end;

end;

 

Then combine them:

 

result = catx(' ', of comment1-comment4);

 

If it's not acceptable to replace the original COMMENT variables, copy them to new variables first and work on the new variables to get RESULT.

View solution in original post


All Replies
Solution
‎12-18-2015 08:29 AM
Super User
Posts: 5,504

Re: IF/Then Statement tying multiple rows together

Posted in reply to UMAnalyst

SImplest would be to change the variables to eliminate duplicates:

 

array comment {4};

do I=1 to 3;

   do J=i+1 to 4;

      if comment{I} = comment{J} then comment{J}=' ';

   end;

end;

 

Then combine them:

 

result = catx(' ', of comment1-comment4);

 

If it's not acceptable to replace the original COMMENT variables, copy them to new variables first and work on the new variables to get RESULT.

Occasional Contributor
Posts: 14

Re: IF/Then Statement tying multiple rows together

Posted in reply to Astounding

Thank you. It's scary to delete stuff, but that's why we have backups.

Super User
Super User
Posts: 7,955

Re: IF/Then Statement tying multiple rows together

Posted in reply to UMAnalyst

Why transpose the data in the first place to to this, then code around the fact that its transposed?  Just do your logic on the normalised data.

Occasional Contributor
Posts: 14

Re: IF/Then Statement tying multiple rows together

I transposed because I need to get from multiple rows per respondent to one row per respondent.

Super User
Super User
Posts: 7,955

Re: IF/Then Statement tying multiple rows together

Posted in reply to UMAnalyst

Perhaps you misunderstand, you can process the data either in long or wide format, it makes no difference.  Personally I would use transposed data only for reporting purposes (apart from a few times).  Its just simpler working with normalised data:

data have;
  length comment $50;
  id=1; 
  comment="This is some text"; output;
  comment="This is some text"; output;
  comment=""; output;
  comment="This is some text"; output;
  id=2; 
  comment="More text like this"; output;
  comment="More text like this"; output;
  comment="Not the same"; output;
  comment="More text like this"; output;
run;

proc sort data=have nodupkey;
  by id comment;
run;

data want (keep=id result);
  set have;
  by id;
  retain result;
  length result $2000;
  if first.id then result="";
  result=catx(" ",result,comment);
  if last.id then output;
run;
Occasional Contributor
Posts: 14

Re: IF/Then Statement tying multiple rows together

Great idea. Thanks again.

Frequent Contributor
Posts: 108

Re: IF/Then Statement tying multiple rows together

Posted in reply to UMAnalyst
you can continue comment1=comment2 and
comment3=comment4 and so on.. as you have only 4 Variables and if you do not understand Do Loops.How ever DL is actually a permanent solution
🔒 This topic is solved and locked.

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

Discussion stats
  • 7 replies
  • 427 views
  • 2 likes
  • 4 in conversation