topic Re: Automating the comparison on fileds in same dataset in SAS Programming

Automating the comparison on fileds in same dataset

siddharthpeesary — Mon, 01 Feb 2016 17:46:21 GMT

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

Re: Automating the comparison on fileds in same dataset

FreelanceReinh — Mon, 01 Feb 2016 18:35:49 GMT

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?

Re: Automating the comparison on fileds in same dataset

siddharthpeesary — Mon, 01 Feb 2016 18:35:38 GMT

yeah I also have character variables
FIELDS= 100+
RECORDS=1000-2000

Re: Automating the comparison on fileds in same dataset

FreelanceReinh — Mon, 01 Feb 2016 18:47:48 GMT

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.

Re: Automating the comparison on fileds in same dataset

siddharthpeesary — Mon, 01 Feb 2016 18:48:37 GMT

Thank you so much ...It worked 🙂

Re: Automating the comparison on fileds in same dataset

Ksharp — Tue, 02 Feb 2016 03:22:44 GMT

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;