Contributor
Posts: 69

# Calculation of Combination of Variable values into counts

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

Super User
Posts: 8,117

## Calculation of Combination of Variable values into counts

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;

PROC Star
Posts: 8,164

## Calculation of Combination of Variable values into counts

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;

Super User
Posts: 10,784

## Calculation of Combination of Variable values into counts

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

Super User
Posts: 10,784

## Calculation of Combination of Variable values into counts

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

Discussion stats
• 4 replies
• 169 views
• 0 likes
• 4 in conversation