DATA Step, Macro, Functions and more

Automating the comparison on fileds in same dataset

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Automating the comparison on fileds in same dataset

[ Edited ]

Hi all,

I have a dataset

A B C D E
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2

 

I have to run a code in such a way that it should give me the list of fields that are 100%matched in one output

ex:

A,D

B,E are same

 

There is any way I can do it.

 

Thanks

 


Accepted Solutions
Solution
‎02-01-2016 01:47 PM
Trusted Advisor
Posts: 1,115

Re: Automating the comparison on fileds in same dataset

[ Edited ]

Thanks for the quick reply. Given this information, I would recommend that you run the suggested code. This will give you the comparisons for numeric variables. Then, to accomplish the same for the character variables, you simply add var _character_; to the first PROC TRANSPOSE step:

proc transpose data=have out=trans;
var _character_;
run;

 

Edit: This assumes that you are not looking for character variables containing "numeric" values which are equal to the values of one of the numeric variables.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,115

Re: Automating the comparison on fileds in same dataset

[ Edited ]

I think, there is a variety of possible approaches. For your sample data the following simple approach works:

data have;
input A B C D E;
cards;
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
;

proc transpose data=have out=trans;
run;

proc sort data=trans nouniquekey;
by col:;
run;

proc transpose data=trans prefix=var out=want(keep=var:);
by col:;
var _name_;
run;

proc print data=want;
run;

Does your real dataset contain character variables? How many observations and variables does it have?

Contributor
Posts: 38

Re: Automating the comparison on fileds in same dataset

yeah I also have character variables
FIELDS= 100+
RECORDS=1000-2000
Solution
‎02-01-2016 01:47 PM
Trusted Advisor
Posts: 1,115

Re: Automating the comparison on fileds in same dataset

[ Edited ]

Thanks for the quick reply. Given this information, I would recommend that you run the suggested code. This will give you the comparisons for numeric variables. Then, to accomplish the same for the character variables, you simply add var _character_; to the first PROC TRANSPOSE step:

proc transpose data=have out=trans;
var _character_;
run;

 

Edit: This assumes that you are not looking for character variables containing "numeric" values which are equal to the values of one of the numeric variables.

Contributor
Posts: 38

Re: Automating the comparison on fileds in same dataset

Thank you so much ...It worked Smiley Happy
Super User
Posts: 9,687

Re: Automating the comparison on fileds in same dataset

That would be very easy if you switch into IML code .

 

 

data have;
input A B C D E;
cards;
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
1 2 3 1 2
;
run;
proc iml;
use have;
read all var _ALL_ into x[c=vnames];
close;
do i=1 to ncol(x);
 match=vnames[i];
 found=0;
 do j=i+1 to ncol(x);
  if all(x[,i]=x[,j]) then do;match=catx(' ',match,vnames[j]);found=1;end;
 end;
 if found then print match;
end;
quit;
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 206 views
  • 0 likes
  • 3 in conversation