topic Re: compare character values in consecutive rows in SAS Programming

compare character values in consecutive rows

USP2405 — Wed, 29 Jul 2015 12:58:55 GMT

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

Re: compare character values in consecutive rows

RW9 — Wed, 29 Jul 2015 13:06:46 GMT

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.

Re: compare character values in consecutive rows

Sonywell — Wed, 29 Jul 2015 13:08:11 GMT

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.

Re: compare character values in consecutive rows

USP2405 — Wed, 29 Jul 2015 13:43:02 GMT

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.

Re: compare character values in consecutive rows

Steelers_In_DC — Wed, 29 Jul 2015 14:06:58 GMT

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;

Re: compare character values in consecutive rows

RW9 — Wed, 29 Jul 2015 14:21:41 GMT

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

...

Re: compare character values in consecutive rows

USP2405 — Fri, 31 Jul 2015 12:33:00 GMT

Thank you Mark.

Re: compare character values in consecutive rows

USP2405 — Fri, 31 Jul 2015 12:35:16 GMT

Thank you