rearrange variables

Reply
Contributor
Posts: 44

rearrange variables

Hi all,

I have the below dataset with questions answered by a group of 10. I am trying to rearrange the variables where the answers match.

example:

Input data is as below:

QuesA1A2A3A4A5A6A7A8A9A10
Q11011111011
Q20110101111
Q31011111011
Q40000000000
Q50100000100
Q61011111011
Q71111111111
Q80100000100
Q91110101111
Q100100000100

The output file should be like the below:  ex: a2 completely matches with a8 (they are together) ,  a4 completely matches with a6,  and  a7,a5,a9,a10 completely match.

A3 and A1 are together as there is a partial match of  Q1,Q3,Q4,Q5, Q6 ,Q7,Q8,Q9,Q10.

QuesA2A8A4A6A7A5A9A10A3A1
Q10011111111
Q21100111110
Q30011111111
Q40000000000
Q51100000000
Q60011111111
Q71111111111
Q81100000000
Q91100111111
Q101100000000

Any ideas in relation to this will be great.

Thanks

Grand Advisor
Posts: 9,584

Re: rearrange variables

Very interesting Question.

data have;
infile cards expandtabs truncover;
input Ques $     A1     A2     A3     A4     A5     A6     A7     A8     A9     A10     ;
cards;;
Q1     1     0     1     1     1     1     1     0     1     1
Q2     0     1     1     0     1     0     1     1     1     1
Q3     1     0     1     1     1     1     1     0     1     1
Q4     0     0     0     0     0     0     0     0     0     0
Q5     0     1     0     0     0     0     0     1     0     0
Q6     1     0     1     1     1     1     1     0     1     1
Q7     1     1     1     1   1   1   1   1   1   1
Q8     0     1     0     0     0     0     0     1     0     0
Q9     1     1     1     0     1     0     1     1     1     1
Q10      0     1     0     0     0     0     0     1     0     0
;
run;
proc transpose data=have out=temp;
var a:;
id Ques;
run;
proc sort data=temp; by Q: ;run;
proc transpose data=temp out=want;
var Q:;
id _NAME_ ;
run;

Xia Keshan

Respected Advisor
Posts: 4,609

Re: rearrange variables

's code will generate lexicographic column order, which might be just fine for your purpose. If however you want an ordering based on the number of differing answers between subjects, so that 0010000000 will not come right after 0001111111, you could try an approach based on the Bray-Curtis distance:


data have;

input Ques :$3. A1 A2 A3 A4 A5 A6 A7 A8 A9 A10;

datalines;

Q1 1 0 1 1 1 1 1 0 1 1

Q2 0 1 1 0 1 0 1 1 1 1

Q3 1 0 1 1 1 1 1 0 1 1

Q4 0 0 0 0 0 0 0 0 0 0

Q5 0 1 0 0 0 0 0 1 0 0

Q6 1 0 1 1 1 1 1 0 1 1

Q7 1 1 1 1 1 1 1 1 1 1

Q8 0 1 0 0 0 0 0 1 0 0

Q9 1 1 1 0 1 0 1 1 1 1

Q10 0 1 0 0 0 0 0 1 0 0

;

proc transpose data=have out=haveT name=subj;

var A1-A10;

id Ques;

run;

proc distance data=haveT out=haveDist method=BRAYCURTIS;

var  ANOMINAL(QSmiley Happy;

id subj;

run;

proc mds data=haveDist dimension=1 out=haveOrder level=ordinal plots=none noprint;

id subj;

run;

proc sql noprint;

select subj into :subjOrder separated by " "

from haveOrder

where _TYPE_ = "CONFIG"

order by dim1, subj;

quit;

data want;

length Ques $3 &subjOrder. 8;

set have;

run;


proc print data=want; run;

PG

PG
Trusted Advisor
Posts: 1,203

Re: rearrange variables

ods output eigenvectors=vector;

proc princomp data=have;

var a:;

run;


proc sql;

select variable into :arrange_vars separated by ' ' from vector

order by prin1;

quit;

data want;

retain ques &arrange_vars;

set have;

run;


proc print data=want;

run;

Ask a Question
Discussion stats
  • 3 replies
  • 265 views
  • 4 likes
  • 4 in conversation