viewdata.sas

Go to the documentation of this file.

/**
  @file viewdata.sas
  @brief Provide the raw view of the data
  @details Pass a LIBDS and FILTER_RK to return a dataset for viewing.
  VIEW datasets include all columns / rows (unlike EDIT, which are filtered
  for current records and don't include the SCD2 etc cols).
 
  <h4> Service Inputs </h4>
 
  <h5> SASCONTROLTABLE </h5>
  |LIBDS:$41.|FILTER_RK:best.|SEARCHTYPE:$4|SEARCHVAL:$100|
  |---|---|---|---
  |DC258467.MPE_X_TEST|-1|CHAR|Some String|
 
  No input column may be longer than the length declared in this service -
  LIBDS 41, SEARCHTYPE 4, SEARCHVAL 100.  An input column longer than the
  declared length stops the step with "Multiple lengths were specified for
  the variable ...", so the SASjs adapter sends the $char form (eg $char4.),
  which also preserves any leading blanks.
 
  <h4> Service Outputs </h4>
 
  <h5> cols </h5>
  @li DDTYPE - C=CHARACTER, N=NUMERIC, else DATE / TIME / DATETIME
  @li FORMAT
  @li LABEL
  @li LENGTH
  @li NAME
  @li TYPE
 
  <h5> sasparams </h5>
  @li FILTER_TEXT
  @li NOBS
  @li PK_FIELDS - string seperated list of primary key fields, if they exist
  @li TABLENAME
  @li TABLEURI
  @li VARS
 
  <h5> versions </h5>
  history of DC versions for this particular table
 
  <h5> viewdata </h5>
  The raw data from the target table.
 
  <h4> SAS Macros </h4>
  @li dc_assignlib.sas
  @li dc_casload.sas
  @li dc_createdataset.sas
  @li dc_gettableid.sas
  @li mf_existds.sas
  @li mf_getvarcount.sas
  @li mf_nobs.sas
  @li mf_verifymacvars.sas
  @li mp_abort.sas
  @li mp_cntlout.sas
  @li mp_getcols.sas
  @li mp_getpk.sas
  @li mp_jsonout.sas
  @li mp_searchdata.sas
  @li mp_validatecol.sas
  @li mpe_columnlevelsecurity.sas
  @li mpe_dsmeta.sas
  @li mpe_getversions.sas
  @li mpe_filtermaster.sas
 
 
  @version 9.2
  @author 4GL Apps Ltd
  @copyright 4GL Apps Ltd.  This code may only be used within Data Controller
    and may not be re-distributed or re-sold without the express permission of
    4GL Apps Ltd.
**/
 
%mpeinit()
 
/* configure macvars */
%global LIBDS FILTER_RK SEARCHVAL SEARCHTYPE FMT_IND;
 
/* avoid code injection */
%let FMT_IND=0;
%let SEARCHTYPE=;
%let SEARCHVAL=;
%let FILTER_RK=;
%let LIBDS=;
 
/**
  * Validate inputs
  */
data work.intest;
  length libds $41 filter_rk 8. searchval $100 searchtype $4;
  set work.SASCONTROLTABLE;
  call symputx('orig_libds',libds);
  /* validate filter_rk */
  if filter_rk le 0 then filter_rk=-1;
 
  /* check if the request is for a format catalog */
  if substr(cats(reverse(libds)),1,3)=:'CF-' then do;
    libds=scan(libds,1,'-');
    putlog "Format Catalog Captured";
    call symputx('fmt_ind',1);
  end;
  putlog (_all_)(=);
 
  /* validate libds */
  %mp_validatecol(LIBDS,LIBDS,is_libds)
 
  if searchtype in ('CHAR','NUM') then do;
    searchval=tranwrd(searchval,'%','');
    searchval=tranwrd(searchval,'&','');
    searchval=tranwrd(searchval,';','');
    searchval=tranwrd(searchval,'"','');
    call symputx('searchtype',searchtype);
    call symputx('searchval',searchval);
  end;
  else if searchtype not in ('','NONE') then do;
    putlog 'ERR' 'OR: Invalid searchtype:' searchtype;
    stop;
  end;
 
  if is_libds=0 then do;
    putlog 'ERR' 'OR: Invalid libds:' libds;
    stop;
  end;
  else do;
    call symputx('filter_rk',filter_rk);
    call symputx('libds',libds);
  end;
  output;
  stop;
run;
 
%mp_abort(iftrue= (%mf_verifymacvars(libds filter_rk fmt_ind)=0)
  ,mac=&_program..sas
  ,msg=%str(Problem with macro inputs)
)
 
%mp_abort(iftrue= (%mf_nobs(work.intest)=0)
  ,mac=&_program
  ,msg=%str(Some err with service inputs)
)
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(syscc=&syscc)
)
 
 
/**
  * assign the Library
  */
%dc_assignlib(READ,%scan(&LIBDS,1,.))
%dc_casload(&libds)
 
/* abort if looking for a format and the catalog doesn't exist */
%mp_abort(iftrue= (&fmt_ind=1 and %sysfunc(exist(&libds,CATALOG))=0)
  ,mac=&_program..sas
  ,msg=%str(Catalog &libds does not exist!)
)
 
/**
  check if dataset can actually be opened - as library may exist but it may not
  be possible to assign, and even if it can, the physical table may not exist
**/
data _null_;
  if &fmt_ind=0 then do;
    dsid=open("&libds");
    rc=close(dsid);
  end;
  else dsid=42;
  call symputx('existds',dsid,'l');
  putlog 'dataset exists check:' dsid;
run;
 
/**
  * get the data
  */
%global dsobs;
%let dsobs=0;
%macro x();
%if not %symexist(DC_MAXOBS_WEBVIEW) %then %do;
  %put NOTE:;%put NOTE- DC_MAXOBS_WEBVIEW not found!;
  %put NOTE- Please add to &mpelib..MPE_CONFIG table;
  %put NOTE-;%put NOTE-;
  %global DC_MAXOBS_WEBVIEW;
  %let DC_MAXOBS_WEBVIEW=500;
%end;
%if &existds>0 %then %do;
 
  %if &fmt_ind=1 %then %do;
    /* export format and point the libds to the output table from here on */
    %mp_cntlout(
      libcat=&libds
      ,fmtlist=0
      ,cntlout=work.fmtextract
    )
    %let libds=WORK.FMTEXTRACT;
    proc datasets lib=work noprint;
      modify FMTEXTRACT;
      index create
        pk_cntlout=(type fmtname fmtrow)
        /nomiss unique;
    quit;
  %end;
 
  proc sql noprint;
  select count(*) into: dsobs from &libds;
 
  %put preparing query;
  %mpe_filtermaster(VIEW,&libds,
    dclib=&mpelib,
    filter_rk=&filter_rk,
    outref=filtref,
    outds=work.query
  )
  %put printing generated filterquery:;
  data _null_;
    infile filtref;
    input;
    putlog _infile_;
  run;
 
  %if &searchtype=NONE or "%trim(&searchtype) " = " " %then %do;
    /* get row count */
    filename rows temp;
    data _null_;
      file rows;
      infile filtref end=eof;
      input;
      if _n_=1 then do;
        put 'proc sql;';
        put "select count(*) into: dsobs from &libds where";
      end;
      put _infile_;
      if eof then put ';';
    run;
    data _null_;
      infile rows;
      input;
      putlog _infile_;
    run;
 
    %inc rows;
 
    /* send actual data, filtered and row-capped */
    data work.viewdata;
      set &libds;
      where %inc filtref;;
      if _n_>&DC_MAXOBS_WEBVIEW then stop;
    run;
    %if %mf_nobs(work.viewdata)=0 %then %do;
      data work.viewdata;
        /* send empty row if empty table to help with hot rendering */
        output;
        set work.viewdata;
      run;
    %end;
  %end;
  %else %do;
    data work.vwsearch/view=work.vwsearch;
      set &libds;
      where %inc filtref;;
    run;
    %if %upcase(&searchtype)=CHAR %then %do;
      %mp_searchdata(lib=work
        ,ds=vwsearch
        ,string=%superq(searchval)
        ,outobs=&DC_MAXOBS_WEBVIEW
      )
    %end;
    %else %if %upcase(&searchtype)=NUM %then %do;
      %mp_searchdata(lib=work
        ,ds=vwsearch
        ,numval=%superq(searchval)
        ,outobs=&DC_MAXOBS_WEBVIEW
      )
    %end;
    %if %mf_existds(libds=MPSEARCH.vwsearch) %then %do;
      %let dsobs=%mf_nobs(MPSEARCH.vwsearch);
      data viewdata;
        set MPSEARCH.vwsearch;
        if _n_<=&DC_MAXOBS_WEBVIEW;
      run;
    %end;
    %else %do;
      %let dsobs=0;
      data viewdata;
        set &libds;
        stop;
      run;
    %end;
  %end;
%end;
%else %do;
  /* physical table is not accessible so create from metatadata definition */
  %dc_createdataset(libds=&libds,outds=viewdata)
  data viewData;
    output;
    set viewdata;
  run;
 
  /* make filtref / work.query / work.groups to avoid downstream issues */
  filename filtref temp;
  data work.query;
    file filtref;
    x=0;
    put x;
  run;
  data work.groups;
    length groupuri groupname $32 groupdesc $128 ;
    call missing (of _all_);
    output;
    stop;
  run;
%end;
%mend x; %x()
 
/* apply column level security */
%mpe_columnlevelsecurity(%scan(&libds,1,.),%scan(&libds,2,.),work.viewdata
  ,mode=VIEW
  ,clsds=&mpelib..mpe_column_level_security
  ,groupds=work.groups /* was created in mpe_filtermaster */
  ,outds=work.viewdata2
  ,outmeta=work.cls_rules
)
 
 
/* get table uri (if sas 9) to enable linking direct to lineage */
%dc_gettableid(libref=%scan(&libds,1,.)
  ,ds=%scan(&libds,2,.)
  ,outds=work.parambase
)
 
data _null_;
  infile filtref end=eof;
  input;
  length filter_text $32767;
  retain filter_text;
  filter_text=catx(' ',filter_text,_infile_);
  if eof then do;
    if cats(filter_text)='1=1' then filter_text='';
    call symputx('filter_text',filter_text);
  end;
run;
 
%mp_getpk(%scan(&libds,1,.), ds=%scan(&libds,2,.), outds=work.pk_fields)
 
%let pk_fields=;
data _null_;
  set work.pk_fields;
  call symputx('pk_fields',pk_fields);
run;
 
data work.sasparams;
  set work.parambase;
  format FILTER_TEXT $32767.;
  FILTER_TEXT=symget('FILTER_TEXT');
  length PK_FIELDS $512;
  PK_FIELDS=symget('PK_FIELDS');
  nobs=&dsobs;
  vars=%mf_getvarcount(viewdata);
  maxrows=&DC_MAXOBS_WEBVIEW;
run;
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(syscc=&syscc)
)
 
%mp_getcols(&libds, outds=cols1)
 
/* trim COLS payload in the SQL below - DDTYPE C=CHARACTER N=NUMERIC
  (dates kept in full), VARNUM dropped (client relies on column order,
  not position) */
proc sql;
create table cols as
  select a.name
      ,a.type
      ,a.length
      ,a.format
      ,a.fmtname
      ,case a.ddtype
        when 'CHARACTER' then 'C'
        when 'NUMERIC' then 'N'
        else a.ddtype end as ddtype
      ,coalesce(b.dd_shortdesc,a.label,a.name) as label
  from cols1 a
  left join &mpelib..mpe_datadictionary
      (where=(&dc_dttmtfmt. < tx_to
      and dd_source ? %upcase("&orig_libds")
      and dd_type='COLUMN')) b
    on scan(b.dd_source,-1,'.')=upcase(a.name);
quit;
 
 
%mpe_dsmeta(&orig_libds, outds=dsmeta)
 
%mpe_getversions(&mpelib,
  %scan(&orig_libds,1,.),
  %scan(&orig_libds,2,.),
  outds=versions
)
 
%webout(OPEN)
%webout(OBJ,cls_rules)
%webout(OBJ,cols)
%webout(OBJ,dsmeta)
%webout(OBJ,query)
%webout(OBJ,sasparams)
%webout(OBJ,versions)
%webout(OBJ,viewData2,fmt=Y,missing=STRING,showmeta=YES,dslabel=viewdata)
%webout(CLOSE)
 
%mpeterm()