Monday, December 15, 2008

Vertical collapse by five methods

******************(1) INPUT STEP***********;
data have;
  input id: $ string: $;
  cards;
  001 aaa 
  001 bbb 
  002 ccccc 
  002 dddd 
  002 eee 
  003 ffff 
  004 gggggg
  ;
run;

*******************(2) CONCATENATION STEP ***********;
***********(2.1) METHOD I: do-loop and substr()***********;
data want1(drop = string);
    length newstring $50.; 
    do _n_ = 1 by 1 until(last.id);
        set have;
        by id notsorted;
        substr(newstring,length(newstring) + 1) = string;
    end;
run;

***********(2.2) METHOD II: Proc Transpose***********;
proc transpose data = have out = _tmp;
   by id;
   var string;
run;

data want2;
   set _tmp;
   newstring = cats(of col:);
   drop _: col:;
run;

***********(2.3) METHOD III: retain statement***********;
data want3(drop = string);
  set have;
  by id notsorted;
  length newstring $50.;
  retain newstring ;
  if first.id then newstring = string;
  else newstring = cats(newstring, string);
  if last.id;
run;

***********(2.4) METHOD IV: Hash table***********;
data _null_;
  length newstring $50;
  if _n_ =1 then do;
  declare hash h();
    h.defineKey('id');
    h.defineData('id', 'newstring');
    h.defineDone();
  end;
  set have end = eof ;
  if h.find() ne 0 then do;
    newstring = string;
    h.add();
  end;
  else do;
    newstring = cats(newstring, string);
    h.replace();
  end;
  if eof then h.output(dataset: 'want4');
run;

***********(2.5) METHOD V: SQL and macro***********;
proc sql noprint;
  select count(unique(id)) into: idnum 
  from have;
  select distinct id into: allid separated by ', ' 
  from have;
quit;

%macro concatenate();
  %let id = scan("&allid", &i);
  %do i = 1 %to &idnum;
    proc sql noprint;
      select string into: newstring separated by ''
      from have
      where id = &id;
    quit;
  %put &newstring;
  %end;
%mend;
%concatenate();

*********************END OF ALL CODING******************************;
References:
1. Technique board. Mysas.net.

Good math, bad engineering

As a formal statistician and a current engineer, I feel that a successful engineering project may require both the mathematician’s abilit...