## compare character values in consecutive rows

Solved
Occasional Contributor
Posts: 15

# compare character values in consecutive rows

Hi,

I have multiple character values in a variable and want to compare each single row with other.

Can anyone please help me?

Thanks

USP

Accepted Solutions
Solution
‎07-29-2015 10:06 AM
Valued Guide
Posts: 863

## Re: compare character values in consecutive rows

If you are only concerned with consecutive records this will work:

Data have;

input y \$ @@;

datalines ;

mostthat morethis morethis morelike mostlike morelike morelike morelike mostlike

;

run;

data want;

set have;

l_y = lag(y);

if l_y = y then flag = 'Same';

else flag = 'Diff';

drop l_y;

run;

All Replies
Super User
Posts: 8,798

## Re: compare character values in consecutive rows

Post some test data in the form of a datastep, with required output.  At a guess, merging, hash, retain/lag, transposing, are all options you could use.

Occasional Contributor
Posts: 15

## Re: compare character values in consecutive rows

I have this sample data:

Data new ;

input y \$ @@;

datalines ;

mostthat morethis morethis morelike mostlike morelike morelike morelike mostlike

;

run;

I want to create a new column which will identify difference of each row.

for e.g. 2nd row is not simillar to 1st but 3rd is simillar to 2nd.

same way 7th is simillar to 6th and 8th is to 7th.but 9th is not simillar to 8th row.

Solution
‎07-29-2015 10:06 AM
Valued Guide
Posts: 863

## Re: compare character values in consecutive rows

If you are only concerned with consecutive records this will work:

Data have;

input y \$ @@;

datalines ;

mostthat morethis morethis morelike mostlike morelike morelike morelike mostlike

;

run;

data want;

set have;

l_y = lag(y);

if l_y = y then flag = 'Same';

else flag = 'Diff';

drop l_y;

run;

Occasional Contributor
Posts: 15

## Re: compare character values in consecutive rows

Posted in reply to Steelers_In_DC

Thank you Mark.

Super User
Posts: 8,798

## Re: compare character values in consecutive rows

Yes, its still not clear your logic.  If you want to know which ones appear in more than one row, then thats simple aggregate:

proc sql;

create table WANT as

select  A.*,

B.CNT

from    WORK.NEW A

left join (select Y,count(Y) as CNT from WORK.NEW group by Y) B

on      A.Y=B.Y;

quit;

However, what do you mean by similar?  Do you need soundex() function or something.  What about identifiers, is there grouping involved.  If you need to find out if one element appears and what specific row it appears on then use monotonic() or assign a variable for _n_, then use this to merge, ie.

from HAVE A

left join (select * from HAVE where Y=A.Y and ID > A.ID) B

...

Occasional Contributor
Posts: 13

## Re: compare character values in consecutive rows

Hello,

I assume you mean you want to compare a variable in each row with the same variable in every other row.  Assuming you don't have an enormous number of rows in your input set, you could perform an Cartesian product (cross join).  Keep in mind this will create a table with count of rows = original count of rows ^ 2, which can quickly become unwieldy.

proc sql;

create table analysis as

select a.*, <compare code>

from have a cross join have b;

Regards.

Occasional Contributor
Posts: 15

## Re: compare character values in consecutive rows

Thank you

🔒 This topic is solved and locked.

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

Discussion stats
• 7 replies
• 786 views
• 3 likes
• 4 in conversation