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:$5.|SEARCHTYPE:$4|SEARCHVAL:$1000
  |---|---|---|---
  |DC258467.MPE_X_TEST|-1|CHAR|Some String|
 
  <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()