note2sas.sas

/*
 *  8/14/01 Richard A DeVenezia
 *
 * Two pass reader for Lotus Notes database export files
 *
 *  5/20/02 RAD Update to handle name values containing double quotes
 *
 * Pass one - Determine maximum length needed for each variable
 * Pass two - Read data into character columns, F1-F<n>
 *
 * Once the data has been read into character variables, a
 * data step can be written to transform desired columns to
 * numeric
 */

%macro note2sas (file=, out=, recordDelim=12);

  %local RAWDATA0  ;

  %if %quote(&file) eq %str () %then %do;
    %put ERROR: file= argument is missing.;
    %goto ByeBye;
  %end;

  %if %quote(&out) eq %str() %then %do;
    %put ERROR: out= argument is missing.;
    %goto ByeBye;
  %end;

  %if not %sysfunc(fileExist(&file)) %then %do;
    %put WARNING: &file does not exist.;
    %goto ByeBye;
  %end;

  %let RAWDATA0 = _0;*%substr(%sysfunc(ranuni(0),9.7),4);

  %*
  %* Pass one - measure data
  %*;

  data &RAWDATA0 (keep=name len);
    infile "&file" length=L end=end;

    length line $200;
    input line $varying. L @;

    if line eq: byte(&recordDelim) then return;

    attrib
      name length=$60
      len format=3.
    ;

    p = index (line, ':  ');
    if p > 1 then do;
      name = substr (line,1,p-1);
      len = L-p-2;
      output;
    end;
  run;

  proc sql;
    create table &RAWDATA0 as
    select name, max(len) as len format=3.
    from &RAWDATA0
    where name ne ' '
    group by name
  ;

  %local i nVars;

  %let nVars = &SQLOBS;
  %do i = 1 %to &nVars;
    %local NAM&i LBL&i LEN&i;
  %end;

  %*
  %* Pass two - read data
  %*;

  data _null_;
    length label $40;

    set &RAWDATA0 end=end;

    label = name;

    call symput ('NAM' || trim(left(put(_n_,3.))), quote(trim(name)));
    call symput ('LBL' || trim(left(put(_n_,3.))), quote(trim(label)));
    call symput ('LEN' || trim(left(put(_n_,3.))), trim(left(put(min(len,200),3.))));
  run;

  data &out;
    infile "&file" length=L;

    attrib
      %do i = 1 %to &nVars;
        F_&i length=$&&LEN&i label=&&LBL&i
      %end;
    ;

    array F F_1-F_&nVars;
    retain F_1-F_&nVars;

    length line $200 name $45 value $200.;
    input line $varying. L @;

    p = index (line, ':');
    if p > 1 then do;
      name  = substr(line,1,p-1);
      vlen = L-p-2;
      input @(p+3) value $varying. vlen; * lines are always name:<space><space>value;

      value = compress (value, '000D'x);

      select (name);
        %do i = 1 %to &nVars;
         when (&&NAM&i) F_&i = value;
        %end;
        otherwise ;
      end;
    end;

    if line =: byte(&recordDelim) then do;
      output;
      do over f; f=''; end;
      return;
    end;

    keep F_1-F_&nVars;
  run;

%ByeBye:

  %if %sysfunc (exist (&RAWDATA0)) %then %do;
    proc delete data=&RAWDATA0;
    run;
  %end;

%mend;