DATA Step, Macro, Functions and more

Calculation of Combination of Variable values into counts

Reply
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:

Var1Var2Var3Var4
LondonNew YorkIrelandBangalore
BangaloreHelsinki
HelsinkiOsloBangalore
BangaloreLondonNew York
OsloHelsinki

Output Table.

BangaloreHelsinkiIrelandLondonNew YorkOslo
Bangalore021221
Helsinki202
Ireland1011
London2102
New York2120
Oslo120


Regards,

Siddhartha

Super User
Super User
Posts: 7,060

Calculation of Combination of Variable values into counts

Posted in reply to 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;

PROC Star
Posts: 7,481

Calculation of Combination of Variable values into counts

Posted in reply to Siddhartha

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,041

Calculation of Combination of Variable values into counts

Posted in reply to Siddhartha

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,041

Calculation of Combination of Variable values into counts

Posted in reply to Siddhartha

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

Ask a Question
Discussion stats
  • 4 replies
  • 154 views
  • 0 likes
  • 4 in conversation