SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to create flag variables to do pair check

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

how to create flag variables to do pair check

Hi,

 

I have a question about creating flag variables to do pair check. To make it simple, I listed a monk table below. It shows whether people went to each of centers, for each person. The center variables are flag variables showing if the person went to that center. (In reality, I have about 100 centers to check.)

 

Person_ID Center1 Center2 Center3
abc 1 0 1
efg 0 1 1
xyz 1 1 1

 

What kind of table I want is listed below. I want to create flag variables showing which pair of centers people went to. It does not matter about which center people go first. For person_ID xyz, this person went to three of the centers, so I want all of the pair-flag variables C1C2, C1C3, C2C3 be 1. 

Person_ID C1C2 C1C3 C2C3
abc 0 1 0
efg 0 0 1
xyz 1 1 1

 

The second table I want is listed below. I want to show how many people went to each pair of centers. From the first step table, we can see only 1 person went to Center1 & Center2, 2 people went to center1 & center3, 2 people went to center2 & center3.

  Center1 Center2 Center3
Center1 NA 1 2
Center2 NA NA 2
Center3 NA NA NA

 

Thank you so much for your help in advance!


Accepted Solutions
Solution
‎03-01-2018 06:13 PM
Super User
Posts: 23,262

Re: how to create flag variables to do pair check

[ Edited ]
Posted in reply to michellel

Try the approach I have outlined here:

https://gist.github.com/statgeek/a5184a4e1678d81e2643#file-sas_distance_matrix-sas

 

I think it's exactly your question.

 

Note that this handles 2X2 comparisons, 3x3 comparisons are much more difficult.

 


michellel wrote:

Hi,

 

I have a question about creating flag variables to do pair check. To make it simple, I listed a monk table below. It shows whether people went to each of centers, for each person. The center variables are flag variables showing if the person went to that center. (In reality, I have about 100 centers to check.)

 

Person_ID Center1 Center2 Center3
abc 1 0 1
efg 0 1 1
xyz 1 1 1

 

What kind of table I want is listed below. I want to create flag variables showing which pair of centers people went to. It does not matter about which center people go first. For person_ID xyz, this person went to three of the centers, so I want all of the pair-flag variables C1C2, C1C3, C2C3 be 1. 

Person_ID C1C2 C1C3 C2C3
abc 0 1 0
efg 0 0 1
xyz 1 1 1

 

The second table I want is listed below. I want to show how many people went to each pair of centers. From the first step table, we can see only 1 person went to Center1 & Center2, 2 people went to center1 & center3, 2 people went to center2 & center3.

  Center1 Center2 Center3
Center1 NA 1 2
Center2 NA NA 2
Center3 NA NA NA

 

Thank you so much for your help in advance!


 

 

 

View solution in original post


All Replies
Super User
Posts: 6,629

Re: how to create flag variables to do pair check

Posted in reply to michellel

Are you prepared to analyze 5,000 additional variables?  That's the number of pairs you will get (approximately) with 100 centers.

Contributor
Posts: 71

Re: how to create flag variables to do pair check

Posted in reply to Astounding

It seems too many variables. Maybe I should list 5000 results as observations instead of variables.

 

Solution
‎03-01-2018 06:13 PM
Super User
Posts: 23,262

Re: how to create flag variables to do pair check

[ Edited ]
Posted in reply to michellel

Try the approach I have outlined here:

https://gist.github.com/statgeek/a5184a4e1678d81e2643#file-sas_distance_matrix-sas

 

I think it's exactly your question.

 

Note that this handles 2X2 comparisons, 3x3 comparisons are much more difficult.

 


michellel wrote:

Hi,

 

I have a question about creating flag variables to do pair check. To make it simple, I listed a monk table below. It shows whether people went to each of centers, for each person. The center variables are flag variables showing if the person went to that center. (In reality, I have about 100 centers to check.)

 

Person_ID Center1 Center2 Center3
abc 1 0 1
efg 0 1 1
xyz 1 1 1

 

What kind of table I want is listed below. I want to create flag variables showing which pair of centers people went to. It does not matter about which center people go first. For person_ID xyz, this person went to three of the centers, so I want all of the pair-flag variables C1C2, C1C3, C2C3 be 1. 

Person_ID C1C2 C1C3 C2C3
abc 0 1 0
efg 0 0 1
xyz 1 1 1

 

The second table I want is listed below. I want to show how many people went to each pair of centers. From the first step table, we can see only 1 person went to Center1 & Center2, 2 people went to center1 & center3, 2 people went to center2 & center3.

  Center1 Center2 Center3
Center1 NA 1 2
Center2 NA NA 2
Center3 NA NA NA

 

Thank you so much for your help in advance!


 

 

 

Super User
Posts: 23,262

Re: how to create flag variables to do pair check

How do you want to handle diagonals? Empty or number of people who visited Center1 in total?

 

data test;
input id $ center1-center3;
cards;
abc    1    0    1
efg    0    1    1
xyz    1    1    1
;
ods output sscp=coocs;
proc corr data=test sscp;
var center1-center3;
run;
proc print data=coocs;
format _numeric_ 8.;
run;

Results:

 

SAS Output

Obs Variable center1 center2 center3
1 center1 2 1 2
2 center2 1 2 2
3 center3 2 2 3
Contributor
Posts: 71

Re: how to create flag variables to do pair check

Thank you so much for your answer above! It is really helpful.

 

For the diagonals, Empty would be better, as I am not interested in the number of people who visited one center in total.

PROC Star
Posts: 1,570

Re: how to create flag variables to do pair check

Posted in reply to michellel

Here is a way to get your pair variables at compile time for array statement. I am sure if you are an intermediate level SAS user you can then write your if then look up statements to populate the pair variables accordingly.

data have;
input Person_ID $	Center1	Center2	Center3;
datalines;
abc	1	0	1
efg	0	1	1
xyz	1	1	1
;




%macro t(n);
%do i=1 %to (&n-1);
%do j=&i+1 %to &n;
%if &j ne &i %then center&i&j;
%end;
%end;
%mend t;

%put %t(3);

data want;
set have;
array t(*) %t(3);
/*---Write your look up statements here---*/
run;

I'll try to work on your complete requirement if I find time

 

 

Contributor
Posts: 71

Re: how to create flag variables to do pair check

Posted in reply to novinosrin

Thank you!

PROC Star
Posts: 1,570

Re: how to create flag variables to do pair check

Posted in reply to michellel

a good array exercise

data have;
input Person_ID $	Center1	Center2	Center3;
datalines;
abc	1	0	1
efg	0	1	1
xyz	1	1	1
;

%macro t;
%global n;
%let n=3 ;/*number of centers parameter*/
%do i=1 %to (&n-1);
	%do j=&i+1 %to &n;
	 center&i.center&j
	%end;
%end;
%mend t;

%put %t ;

data want;
set have;
array k(*) %t;
array t(*) center1-center3;
_c=0;
do _i=1 to (&n-1);
do _j=_i+1 to &n;
_c=_c+1;
k(_c)=t(_i)+t(_j);
if k(_c)>1 then k(_c)=1;else k(_c)=0;
end;
end;
drop _:;
run;

in a datastep:

 

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 234 views
  • 1 like
  • 4 in conversation