Calculation of Combination of Variable values into counts

[Siddhartha]

Hi,

I am facing some challenges in converting the following dataset into the table mentioned below using SAS.

Please try to give some suggestions on this.

Dataset:

Var1	Var2	Var3	Var4
London	New York	Ireland	Bangalore
Bangalore	Helsinki
Helsinki	Oslo	Bangalore
Bangalore	London	New York
Oslo	Helsinki

Output Table.

	Bangalore	Helsinki	Ireland	London	New York	Oslo
Bangalore	0	2	1	2	2	1
Helsinki	2	0				2
Ireland	1		0	1	1
London	2		1	0	2
New York	2		1	2	0
Oslo	1	2				0

Regards,

Siddhartha

[Tom]

data have ;

  infile cards dsd truncover;

  length var1-var4 $15 ;

  input var1-var4 &;

cards;

London,New York,Ireland,Bangalore

Bangalore,Helsinki 

Helsinki,Oslo,Bangalore

Bangalore,London,New York

Oslo,Helsinki

run;

data vertical ;

  set have;

  array v var1-var4 ;

  do i=1 to 4 while(v(i) ne ' ');

  do j=i+1 to 4 while(v(j) ne ' ');

    city1=v(i);

    city2=v(j);

    output;

    city1=v(j);

    city2=v(i);

    output;

  end; end;

run;

proc freq data=vertical;

  tables city1*city2 ;

run;

[art297]

I'd take Tom's approach one step further and add in a proc format and use proc summary rather than proc freq.  E.g.:

data have ;

  infile cards dsd truncover;

  length var1-var4 $15 ;

  input var1-var4 &;

cards;

London,New York,Ireland,Bangalore

Bangalore,Helsinki

Helsinki,Oslo,Bangalore

Bangalore,London,New York

Oslo,Helsinki

;

data vertical ;

  set have;

  array v var1-var4 ;

  do i=1 to 4 while(v(i) ne ' ');

    do j=i+1 to 4 while(v(j) ne ' ');

      city1=v(i);

      city2=v(j);

      output;

      city=v(j);

      city2=v(i);

      output;

    end;

  end;

run;

proc format;

  value $cities

  "Bangalore"="Bangalore"

  "Helsinki"="Helsinki"

  "Ireland"="Ireland"

  "London"="London"

  "New York"="New York"

  "Oslo"="Oslo" ;

run;

proc summary data=vertical nway completetypes;

  class city city2;

  output out=need (drop=_type_ rename=(_freq_=count));

  format city $cities.;

run;

data need;

  set need;

  if city eq city2 then count=0;

run;

proc transpose data=need

  out=want (drop=_:

    rename=(

     col1=Bangalore

     col2=Helsinki

     col3=Ireland

     col4=London

     col5=New_York

     col6=Oslo

     ));

  by city;

run;

[Ksharp]

Are you sure you need a dataset like this. If it were ,How to name the first column ?

data have ;
  infile cards dsd truncover;
  length var1-var4 $15 ;
  input var1-var4 &;
cards;
London,New York,Ireland,Bangalore
Bangalore,Helsinki 
Helsinki,Oslo,Bangalore
Bangalore,London,New York
Oslo,Helsinki
;
run;

data vertical ;
  set have;
  array v{*} $ 15  var1-var4 ;
  do i=1 to dim(v);
   do j=1 to dim(v);
    if i ne j and not missing(v{i}) and not missing(v{j}) then do;
                    city1=v{i};city2=v{j};
                    output;
                    end;
  end; end;
run;
options missing='0';
proc tabulate data=vertical format=best8.;
class city1 city2;
table city1=' ',city2=' ';
keylabel n=' ';
run;

Ksharp

[Ksharp]

It is dataset version.

data have ;
  infile cards dsd truncover;
  length var1-var4 $15 ;
  input var1-var4 &;
cards;
London,New York,Ireland,Bangalore
Bangalore,Helsinki 
Helsinki,Oslo,Bangalore
Bangalore,London,New York
Oslo,Helsinki
;
run;

data vertical ;
  set have;
  array v{*} $ 15  var1-var4 ;
  do i=1 to dim(v);
   do j=1 to dim(v);
    if i ne j and not missing(v{i}) and not missing(v{j}) then do;
                    city1=v{i};city2=v{j};
                    output;
                    end;
  end; end;
run;
proc freq noprint;
 tables city1*city2/list nocum nopercent out=x;
run;

proc transpose data=x out=want(drop=_:);
by city1;
var count;
id city2;
run;

proc sql noprint;
 select name into : list separated by ' '
  from dictionary.columns 
   where libname='WORK' and memname='WANT' and name ne 'city1'
    order by name;
quit;
data want(drop=i);
 retain city1 &list;
 set want;
 array v{*} $ 15 &list;
 do i=1 to dim(v);
  if missing(v{i}) then v{i}=0;
 end;
run;

Ksharp