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
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;
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;
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
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.