BookmarkSubscribeRSS Feed
Siddhartha
Calcite | Level 5

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:

Var1Var2Var3Var4
LondonNew YorkIrelandBangalore
BangaloreHelsinki
HelsinkiOsloBangalore
BangaloreLondonNew York
OsloHelsinki

Output Table.

BangaloreHelsinkiIrelandLondonNew YorkOslo
Bangalore021221
Helsinki202
Ireland1011
London2102
New York2120
Oslo120


Regards,

Siddhartha

4 REPLIES 4
Tom
Super User Tom
Super User

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
Opal | Level 21

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
Super User

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
Super User

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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