topic Calculation of Combination of Variable values into counts in SAS Programming

Calculation of Combination of Variable values into counts

Siddhartha — Thu, 22 Dec 2011 13:15:05 GMT

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

Calculation of Combination of Variable values into counts

Tom — Thu, 22 Dec 2011 13:33:13 GMT

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;

Calculation of Combination of Variable values into counts

art297 — Thu, 22 Dec 2011 14:06:18 GMT

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;

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;

Calculation of Combination of Variable values into counts

Ksharp — Fri, 23 Dec 2011 06:46:55 GMT

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

Calculation of Combination of Variable values into counts

Ksharp — Fri, 23 Dec 2011 07:21:01 GMT

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