SAS Iteration and comparison with next value

Reply
Occasional Contributor
Posts: 7

SAS Iteration and comparison with next value

Hello Everyone,

I am new to this forum.

I have a dataset which is want to check for many-to-many functions.

There are two columns A and B.

I wish to write a code that compares two successive values of A (let n and n+1) and if they are equal, it compares the corresponding two values in B, which, if different prints the value of A.

I have PROC SORTED and DEDUPLICATED the dataset on A and B but now I am not sure how to loop the code. I tried first. and last. but I couldn't write the full code.

Please help.

Best Regards,

Super User
Posts: 5,257

Re: SAS Iteration and comparison with next value

Please provide some sample data (input and desired output) which could help understand what you wish to achieve.

Data never sleeps
Super User
Posts: 5,085

Re: SAS Iteration and comparison with next value

Assuming you have sorted BY A B and deduped (and assuming I interpreted the question properly), this should give you what you want:

data want;

set have;

by A B;

if (first.A=0 or last.A=0);

run;

This actually gives you all the B values that correspond to that value of A.  If you want a smaller set, such as just the A values with multiple B values, subset with:

if first.A=1 and last.A=0;


Good luck.

Occasional Contributor
Posts: 7

Re: SAS Iteration and comparison with next value

Let the dataset be as follows:

A|B

D|5

D|6

E|6

E|7

F|8

G|10

As you can see that this is a many-to-many dataset which values of A going to multiple values of B (D goes to 5 and 6) (6 goes to D and E) etc.

I wish to print all values of A for which there are multiple corresponding values of B.

Regards,

Kavindra...

Super User
Posts: 5,257

Re: SAS Iteration and comparison with next value

"... multiple corresponding values of B." Still not fully understand what your desired output will look like.

Please provide the corresponding result set to your input example.

Data never sleeps
Super Contributor
Posts: 578

Re: SAS Iteration and comparison with next value

proc sql;

create table work.want as

select * from work.have

where col_a in (select col_a from work.have group by col_a having count(*)>1);

quit;

This looks like one-to-many rather than many-to-many.

Do you have this case as well (which would imply many-to-many);

A|1

A|2

B|1

B|3

Occasional Contributor
Posts: 7

Re: SAS Iteration and comparison with next value

The output would be:

D|5

D|6

E|6

E|7

because these values of column A have multiple values in column B corresponding to them.

Regards,

Kavindra

Regular Contributor
Posts: 195

Re: SAS Iteration and comparison with next value

Hi,

How about the following one...

proc sql;  

   create table want as

   select a.*

   from have as a,

       (select a,b,count(b) as tot

              from have

       group by 2         

       having tot > 1) as b

   where a.a = b.a;

quit;

-Urvish

Super Contributor
Posts: 339

Re: SAS Iteration and comparison with next value

Soln1, use _temporary_ arrays to retain values from previous data step iterations

data want;

     set have;

     if _N_=1 then do;

          array prev_A{1} $1. _temporary_;

          array prev_B{1} 8. _temporary_;

     prev_A{1}=A;

     prev_B{1}=B;

     end;

     else do;

         if A=prev_A{1} then do;

               if B NE prev_B{1} then output;

               else; /* if you wanted to split outputs when both As and Bs are equal */

         end;

     prev_A{1}=A;

     prev_B{1}=B;

     end;

     drop B;

run;


Soln2 use the less friendly lag function. It is a pile so if you want to work with more than a few lines, it becomes very hectic to write although it provides the easiest solution for this very simple n/n+1 case.

data want2;

     set have;

     if A=lag(A) and B NE lag(B) then output;

     drop B;

run;

Vince

Regular Contributor
Posts: 195

Re: SAS Iteration and comparison with next value

Your code will produce the output like this...

A

D

E

But based on the sample output requested by the user is like...

A B

D 5

D 6

E 6

E 7

so i think my SAS Code provides the same output which user wants...i am not saying that your solution is not valid...

-Urvish

Super Contributor
Posts: 339

Re: SAS Iteration and comparison with next value

I'm sorry I missed out the OPs third reply with his desired output which does not correspond to:

I wish to write a code that compares two successive values of A (let n and n+1) and if they are equal, it compares the corresponding two values in B, which, if different prints the value of A.

Which my solution was based upon. However, your solution may produce undesired results

data have;

     input @1 A $1. @3 B 8.;

     datalines;

D 5

D 6

E 6

E 7

F 7

G 10

;

run;

All E lines get duplicated because there is a match on B for more than one value

F gets output even though it is unique because your rule only treats column B.

Depending on his data it may or may not work as intended.

Vince

Super Contributor
Posts: 339

Re: SAS Iteration and comparison with next value

So as to provide solution that should've worked without removing duplicates and that's a general approach to comparing each row with the previous and/or the next one in a dataset:

data want;

     if _n_=1 then do;

          dsid=open('have(rename=(a=aa b=bb))');

          call set(dsid);

     end;

     set have end=last;

     if fetchobs(dsid, _n_+1, "ABS SET")=0 then do; /* Look next row */

          if a=aa and b NE bb then output;

          else if fetchobs(dsid, _N_-1, "ABS SET")=0 then do;

               if a=aa and b NE bb then output; /* look previous row */

               end; 

     end;

     if last then rc=close(dsid);

     retain dsid;

     drop aa bb dsid dsid2 rc;

     retain dsid;

run;

Fairly dense syntax. last. and first. wouldn't have enabled you to achieve your desired results since you want to print rows where there was a match based on both previous or next values. However, Hai.Kuo's approach is probably the simplest way to get desired results since you've already sorted and killed dupes in your dataset as then anytime the count on by variable a GT 1, you know that all B values will respect the condition to not be equal for each row.

I've done it to self-learn fetch, hope it helps someone else out there.

Vince

Super User
Posts: 5,085

Re: SAS Iteration and comparison with next value

Vince,

Why do you say first. and last. wouldn't work?  My original five-line solution works just fine.  What am I missing?

Respected Advisor
Posts: 3,124

Re: SAS Iteration and comparison with next value

,

Very nice. First and Last actually hint some information on back and forward rows, and I admit that it is sufficient to solve OP's problem in one pass:

data want;

set have;

by A B;

if first.a * last.a ne 1;

run;

Thank you!

Haikuo

Super Contributor
Posts: 339

Re: SAS Iteration and comparison with next value

Hi Astounding,

I'm sorry if I wasn't clear enough. I was referring to more general solutions that require information from next and or previous observations. The OP's data structure was simple enough and had the beautiful property of being sorted and duplicates removed. However, someone could very well have to do similar operations, at least conceptually where you need information from previous or further rows in your DS in a fixed position relative to the current row.

Your solution is very clever given the specifications but is hardly extensible. Add duplicates and you are printing all duplicates within a by A. Remove sorting and by processing is out. As I said, I wanted to learn to use fetch and play around between/within the same dataset and figured I'd provide my solution as sometimes the data structure will not have as great properties yet you may still need to do similar conditioning on previous/next rows.

Vince

Ask a Question
Discussion stats
  • 26 replies
  • 551 views
  • 4 likes
  • 9 in conversation