loadfile.sas

Go to the documentation of this file.

/**
  @file loadfile.sas
  @brief Loads a file
  @details
 
  <h4> SAS Macros </h4>
  @li mddl_sas_cntlout.sas
  @li mp_abort.sas
  @li mf_getplatform.sas
  @li mf_getuser.sas
  @li mf_getvarlist.sas
  @li mf_mkdir.sas
  @li mf_verifymacvars.sas
  @li mf_wordsinstr1butnotstr2.sas
  @li dc_assignlib.sas
  @li mpe_getgroups.sas
  @li mp_lockfilecheck.sas
  @li mpe_loader.sas
  @li mp_cleancsv.sas
  @li mp_binarycopy.sas
  @li mp_validatecol.sas
  @li mpeinit.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.
 
**/
 
%global table dlm;
%mpeinit(fetch=NO)
 
%global _WEBIN_FILENAME1 _WEBIN_FILENAME2
  _WEBIN_FILEREF _WEBIN_FILEREF1 _WEBIN_FILEREF2;
%macro load();
%if %mf_getplatform()=SASVIYA %then %do;
  %global _webin_fileuri _webin_fileuri1 _webin_fileuri2;
  %let _webin_fileuri1=%sysfunc(coalescec(&_webin_fileuri1,&_webin_fileuri));
  %if "&_webin_fileuri1" ne "" %then %do;
    %put &=_webin_fileuri1;
    filename sjfref1 filesrvc "&_webin_fileuri1";
    %let _WEBIN_FILEREF1=sjfref1;
  %end;
  %if "&_webin_fileuri2" ne "" %then %do;
    %put &=_webin_fileuri2;
    filename sjfref2 filesrvc "&_webin_fileuri2";
    %let _WEBIN_FILEREF2=sjfref2;
  %end;
%end;
%mend load;
%load()
 
%let _WEBIN_FILENAME1=%sysfunc(coalescec(&_WEBIN_FILENAME1,&_WEBIN_FILENAME));
%let _WEBIN_FILEREF1=%sysfunc(coalescec(&_WEBIN_FILEREF1,&_WEBIN_FILEREF));
 
%let abort=0;
/* we do not know if the excel file will be first or second fileref */
data _null_;
  ext1=upcase(scan(symget('_WEBIN_FILENAME1'),-1,'.'));
  ext2=upcase(scan(symget('_WEBIN_FILENAME2'),-1,'.'));
  if ext1='CSV' then do;
    csvname=symget('_WEBIN_FILENAME1');
    csvref=symget('_WEBIN_FILEREF1');
    xlsname=symget('_WEBIN_FILENAME2');
    xlsref=symget('_WEBIN_FILEREF2');
  end;
  else if ext2='CSV' then do;
    csvname=symget('_WEBIN_FILENAME2');
    csvref=symget('_WEBIN_FILEREF2');
    xlsname=symget('_WEBIN_FILENAME1');
    xlsref=symget('_WEBIN_FILEREF1');
  end;
  else call symputx('abort',1);
 
  call symputx('csvname',csvname);
  call symputx('csvref',csvref);
  call symputx('xlsname',xlsname);
  call symputx('xlsref',coalescec(xlsref,'0'));
run;
 
%mp_abort(iftrue= (&abort=1)
  ,mac=&_program
  ,msg=%str(File "&csvname" or "&xlsname" must be a CSV!
    (Comma separated with .csv extension))
)
 
 
%let user=%mf_getuser();
 
%mp_abort(
  iftrue=(%mf_verifymacvars(table)=0)
  ,mac=&_program
  ,msg=%str(Missing: table)
)
 
%let table=%upcase(%trim(&table));
 
/* load parameters */
data _null_;
  libds=upcase(symget('table'));
  call symputx('orig_libds',libds);
  call symputx('orig_lib',scan(libds,1,'.'));
  call symputx('orig_ds',scan(libds,2,'.'));
  is_fmt=0;
  if substr(cats(reverse(libds)),1,3)=:'CF-' then do;
    libds=scan(libds,1,'-');
    putlog "Format Catalog Captured";
    libds='work.fmtextract';
    call symputx('libds',libds);
    call execute('%mddl_sas_cntlout(libds=work.fmtextract)');
    is_fmt=1;
  end;
  else call symputx('libds',libds);
  call symputx('is_fmt',is_fmt);
  /* validate libds to prevent code injection */
  %mp_validatecol(LIBDS,LIBDS,is_libds)
  if is_libds=0 then do;
    putlog 'ERR' 'OR: Invalid libds:' libds;
    call symputx('bad_libds',1);
  end;
  else call symputx('bad_libds',0);
  putlog (_all_)(=);
run;
 
%mp_abort(iftrue= (&bad_libds=1)
  ,mac=&_program
  ,msg=%str(Invalid libds supplied)
)
 
/* check that the user has the requisite access */
%mpe_getgroups(user=&user,outds=groups)
 
proc sql;
create table accesscheck as
select * from groups
  where groupname="&mpeadmins"
    or groupname in (select sas_group from &mpelib..mpe_security
        where &dc_dttmtfmt. lt tx_to
          and access_level="EDIT"
          and (
            (libref="&orig_lib" and dsn="&orig_ds")
            or (libref="&orig_lib" and dsn="*ALL*")
            or (libref="*ALL*" and dsn="*ALL*")
            or (libref="*ALL*" and dsn="&orig_ds")
          ));
%let nobs=;
select count(*) into: nobs from &syslast;
%mp_abort(iftrue= (&nobs=0)
  ,mac=&sysmacroname
  ,msg=%str(&user not authorised to load &orig_libds per &mpelib..mpe_security)
)
 
%dc_assignlib(WRITE,&orig_lib)
 
%mp_abort(iftrue= (&syscc ge 4)
  ,mac=loadfile
  ,msg=%str(Issue assigning library &orig_lib)
)
 
%global txfrom txto processed rk;
 
data _null_;
  set &mpelib..MPE_TABLES;
  where libref="&orig_lib" and dsn="&orig_ds";
  call symputx('txfrom',var_txfrom);
  call symputx('txto',var_txto);
  call symputx('processed',var_processed);
  if not missing(RK_UNDERLYING) then call symputx('rk',buskey);
run;
 
%mp_lockfilecheck(libds=&orig_libds)
 
data compare;
  set &libds(drop=&txfrom &txto &processed &rk);
  stop;
run;
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(syscc=&syscc line 80)
)
 
/* get line terminator, assume it's the first cr, lf, or crlf */
data _null_;
  length text $32767 term $4;
  call missing (of _all_);
  fid=fopen("&csvref",'I',32767,'b');
  rc=fread(fid);
  rc2=fget(fid,text,32767);
  cr=find(text,'0D'x );
  lf=find(text,'0A'x );
  crlf=find(text,'0D0A'x);
  rc=fclose(fid);
  if crlf>0 & cr<crlf then term='CR';
  else if crlf>0 & crlf<lf then term='CRLF';
  else if lf>0 & cr>0 & lf<cr then term='LF';
  else if lf>0 then term='LF';
  else term='CR';
  call symputx('termstr',term);
run;
 
data _null_;
  infile &csvref lrecl=32000 dsd termstr=&termstr;
  input;
  length incols_unsorted $32000 dlm $1;
  incols_unsorted=compress(upcase(_infile_),"'"!!'"');
  /* dlm has length 1 so will be the first non alpha / digit char */
  /* expectation is that there will not be any crazy characters in first col! */
  dlm=compress(incols_unsorted,'_ ','ad');
  incols_unsorted=compress(incols_unsorted,dlm!!'_','kado');
  incols_unsorted=tranwrd(incols_unsorted,dlm,' ');
  call symputx('incols_unsorted',incols_unsorted);
  call symputx('dlm',dlm);
  putlog incols_unsorted=;
  putlog dlm=;
  stop;
run;
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(syscc=&syscc line 99)
)
 
%let basecols=%upcase(%mf_getvarlist(work.compare,dlm=%str( )));
 
%let missing_cols=%trim(
    %mf_wordsInStr1ButNotStr2(
      Str1=&basecols
      ,Str2=&incols_unsorted
    ));
 
%let msg=
  Expected cols: <b>&basecols</b>
  </br>Received cols: <b>&incols_unsorted</b>
  </br>Missing cols: <b>&missing_cols</b>
;
%mp_abort(iftrue= (%length(%trim(&missing_cols)) > 1 or &syscc ne 0)
  ,mac=mpestp_loadfile.sas
  ,msg=%superq(msg)
)
%let msg=0;
 
 
 
PROC FORMAT;
  picture yymmddhhmmss other='%0Y%0m%0d_%0H%0M%0S' (datatype=datetime);
RUN;
 
/* create a dataset key (datetime plus 6 digit random number plus PID) */
%let mperef=DC%left(%sysfunc(datetime(),B8601DT19.3))_%substr(
  %sysfunc(ranuni(0)),3,6)_%substr(%str(&sysjobid    ),1,4);
 
/* Create package folder and redirect the log */
%let dir=&mpelocapprovals/&mperef;
%mf_mkdir(&dir)
 
/* clean embedded line breaks and force CRLF line endings */
%mp_cleancsv(in=&csvref, out=&dir/&orig_libds..csv)
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program..sas
  ,msg=%str(issue in mp_cleancsv)
)
 
%put; %put; %put log is being redirected;
%let url=_program=%substr(&_program
  ,1,%length(&_program)-8)getlog%nrstr(&)table=&mperef;
%put to retrieve, visit this url:; %put;%put;
%put &url;
%put;
 
/* proc printto log="&dir/weblog.txt";run; */
libname approve "&dir";
 
options mprint;
%put &=mperef;
%put &=termstr;
%put &=dlm;
 
%mpe_loader(mperef=&mperef
  ,submitted_reason_txt=%quote(File upload:  %superq(csvname))
  ,dlm=%superq(dlm)
  ,url=%superq(url)
  ,termstr=CRLF
  ,dc_dttmtfmt=&dc_dttmtfmt
)
/* mp_abort cannot exit cleanly from inside a %include, so an abort there
  (eg in a hook script run via mp_include) only records the error in
  work.mp_abort_errds.  mode=INCLUDE picks that up and aborts properly here. */
%mp_abort(mode=INCLUDE)
 
%mp_abort(
  iftrue= (%sysfunc(fileexist(%sysfunc(pathname(work))/mf_abort.error)) ne 0)
  ,mac=&_program
  ,msg=%nrstr(Problem occurred in &sysmacroname (mf_abort.error file found))
)
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=mpestp_loadfile.sas
  ,msg=%str(syscc=&syscc)
)
 
filename outref "&dir/BKP_&xlsname";
 
data _null_;
  if "&xlsref" ne "0" then do;
    rc=fcopy("&xlsref","outref");
  end;
run;
 
/**
  * if running 9.3 or older, delete step above and enable macro below
%mp_binarycopy(iftrue=("&xlsref" ne "0"),inref=&xlsref,outref=outref)
  */
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&sysmacroname
  ,msg=%str(syscc=&syscc when backing up source file &xlsname)
)
 
data sasparams;
  STATUS='SUCCESS';
  DSID="&mperef";
run;
 
%webout(OPEN)
%webout(OBJ,sasparams)
%webout(CLOSE)
 
 
%mpeterm()