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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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;

View solution in original post

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

USP2405
Obsidian | Level 7

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.

Steelers_In_DC
Barite | Level 11

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;

USP2405
Obsidian | Level 7

Thank you Mark.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

...

Sonywell
Fluorite | Level 6

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.

USP2405
Obsidian | Level 7

Thank you

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
  • 7 replies
  • 4450 views
  • 3 likes
  • 4 in conversation