topic Re: Finding the matching in two columns? in SAS Programming

Finding the matching in two columns?

ybz12003 — Mon, 17 Feb 2020 18:40:24 GMT

Hello,

I have a dataset x with tempID_1 to 3. I would like to do the following steps:

1. If TempID_3 are duplicates, and TempID_1 are the same IDs, then the comment is the same.

2. Otherwise, it's not.

Data Y is the result I am looking for. Thanks.

data x;
      infile datalines dsd;
  input TempID_1 : $15. TempID_2 : $15.  TempID_3 : $15. ;
datalines;
21156, 8946, 7852,  
21156, 8956, 7852, 
24597, 1596, 4113,
10235, 0546, 4113,
C1156, 4286, 0008, 
200, X1O23, UV689,
208, 8MO23, UV689,
579, HL2489, GJ627,
C1156, 4986, 0008,
21156, 8956, 7852, 
;

data y;
      infile datalines dsd;
  input TempID_1 : $15. TempID_2 : $15.  TempID_3 : $15. Comment : $15.;
datalines;
21156, 8946, 7852, same, 
21156, 8956, 7852, same,
24597, 1596, 4113, Diff,
10235, 0546, 4113, Diff,
C1156, 4286, 0008, same,
200, X1O23, UV689, Diff,
208, 8MO23, UV689, Diff,
579, HL2489,GJ627, same,
C1156, 4986, 0008, same,
21156,  , 7852, same,
;

Re: Finding the matching in two columns?

novinosrin — Mon, 17 Feb 2020 19:35:39 GMT

HI @ybz12003 See if this helps


data x;
      infile datalines dsd;
  input TempID_1 : $15. TempID_2 : $15.  TempID_3 : $15. ;
datalines;
21156, 8946, 7852,  
21156, 8956, 7852, 
24597, 1596, 4113,
10235, 0546, 4113,
C1156, 4286, 0008, 
200, X1O23, UV689,
208, 8MO23, UV689,
579, HL2489, GJ627,
C1156, 4986, 0008,
21156, 8956, 7852, 
;

proc sql;
create table want(drop=rn) as
select *,ifc(count(distinct tempid_1)=1,'same','Diff') as Comment
from (select *,monotonic() as rn from x)
group by tempid_3
order by rn;
quit;

Kind Regards!

Re: Finding the matching in two columns?

ybz12003 — Mon, 17 Feb 2020 20:54:04 GMT

Thank you!