content: #
  # Copyright SAS Institute
  #
  #  Licensed under the Apache License, Version 2.0 (the License);
  #  you may not use this file except in compliance with the License.
  #  You may obtain a copy of the License at
  #
  #      http://www.apache.org/licenses/LICENSE-2.0
  #
  #  Unless required by applicable law or agreed to in writing, software
  #  distributed under the License is distributed on an "AS IS" BASIS,
  #  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  #  See the License for the specific language governing permissions and
  #  limitations under the License.
  #
  
  import datetime
  import csv
  import io
  import numbers
  import os
  import shlex
  import sys
  import warnings
  
  try:
      from win32com.client import dynamic
  except ImportError:
      pass
  
  try:
      import pandas as pd
  except ImportError:
      pass
  
  
  class SASConfigCOM(object):
      """
      This object is not intended to be used directly. Instantiate a SASSession
      object instead.
      """
      NO_OVERRIDE = ['kernel', 'sb']
  
      def __init__(self, **kwargs):
          self._kernel = kwargs.get('kernel')
  
          session = kwargs['sb']
          sascfg = session.sascfg.SAScfg
          name = session.sascfg.name
          cfg = getattr(sascfg, name)
          opts = getattr(sascfg, 'SAS_config_options', {})
          outs = getattr(sascfg, 'SAS_output_options', {})
  
          self.host = cfg.get('iomhost')
          self.port = cfg.get('iomport')
          self.user = cfg.get('omruser')
          self.pw = cfg.get('omrpw')
          self.authkey = cfg.get('authkey')
          self.class_id = cfg.get('class_id', '440196d4-90f0-11d0-9f41-00a024bb830c')
          self.provider = cfg.get('provider')
          self.encoding = cfg.get('encoding', '')
  
          self.output = outs.get('output', 'html5')
  
          self.verbose = opts.get('verbose', True)
          self.verbose = kwargs.get('verbose', self.verbose)
  
          self._lock = opts.get('lock_down', True)
          self._prompt = session.sascfg._prompt
  
          if self.authkey is not None:
              self._set_authinfo()
  
          for key, value in filter(lambda x: x[0] not in self.NO_OVERRIDE, kwargs.items()):
              self._try_override(key, value)
  
      def _set_authinfo(self):
          """
          Attempt to set the session user's credentials based on provided
          key to read from ~/.authinfo file. See .authinfo documentation
          here: https://documentation.sas.com/api/docsets/authinfo/9.4/content/authinfo.pdf.
  
          This method supports a subset of the .authinfo spec, in accordance with
          other IO access methods. This method will only parse `user` and `password`
          arguments, but does support spaces in values if the value is quoted. Use
          python's `shlex` library to parse these values.
          """
          if os.name == 'nt':
              authfile = os.path.expanduser(os.path.join('~', '_authinfo'))
          else:
              authfile = os.path.expanduser(os.path.join('~', '.authinfo'))
  
          try:
              with open(authfile, 'r') as f:
                  # Take first matching line found
                  parsed = (shlex.split(x, posix=False) for x in f.readlines())
                  authline = next(filter(lambda x: x[0] == self.authkey, parsed), None)
  
          except OSError:
              print('Error trying to read {}'.format(authfile))
              authline = None
  
          if authline is None:
              print('Key {} not found in authinfo file: {}'.format(self.authkey, authfile))
          elif len(authline) < 5:
              print('Incomplete authinfo credentials in {}; key: {}'.format(authfile, self.authkey))
          else:
              # Override user/pw if previously set
              # `authline` is in the following format:
              #   AUTHKEY username USERNAME password PASSWORD
              self.user = authline[2]
              self.pw = authline[4]
  
      def _try_override(self, attr, value):
          """
          Attempt to override a configuration file option if `self._lock` is
          False. Otherwise, warn the user.
          :param attr: Configuration attribute.
          :param value: Configuration value.
          """
          if self._lock is False:
              setattr(self, attr, value)
          else:
              err = "Param '{}' was ignored due to configuration restriction".format(attr)
              print(err, file=sys.stderr)
  
  
  class SASSessionCOM(object):
      """
      Initiate a connection to a SAS server and provide access for Windows
      clients without the Java dependency. Utilizes available COM objects for
      client communication with the IOM interface.
      It may be possible to communicate with local SAS instances as well,
      although this is functionality is untested. A slight change may be
      required to the `_startsas` method to support local instances.
      """
      SAS_APP = 'SASApp'
      HTML_RESULT_FILE = 'saspy_results.html'
  
      # SASObjectManager.Protocols Enum values
      PROTOCOL_COM = 0
      PROTOCOL_IOM = 2
  
      # SAS Date/Time/Datetime formats
      FMT_DEFAULT_DATE_NAME = 'E8601DA'
      FMT_DEFAULT_DATE_LENGTH = 10
      FMT_DEFAULT_DATE_PRECISION = 0
      FMT_DEFAULT_TIME_NAME = 'E8601TM'
      FMT_DEFAULT_TIME_LENGTH = 15
      FMT_DEFAULT_TIME_PRECISION = 6
      FMT_DEFAULT_DATETIME_NAME = 'E8601DT'
      FMT_DEFAULT_DATETIME_LENGTH = 26
      FMT_DEFAULT_DATETIME_PRECISION = 6
  
      # Pandas data types
      PD_NUM_TYPE = ('i', 'u', 'f', 'c')
      PD_STR_TYPE = ('S', 'U', 'V')
      PD_DT_TYPE = ('M')
      PD_BOOL_TYPE = ('b')
  
      # ADODB RecordSet CursorTypeEnum values
      CURSOR_UNSPECIFIED = -1
      CURSOR_FORWARD = 0
      CURSOR_KEYSET = 1
      CURSOR_DYNAMIC = 2
      CURSOR_STATIC = 3
  
      # ADODB RecordSet LockTypeEnum values
      LOCK_UNSPECIFIED = -1
      LOCK_READONLY = 1
      LOCK_PESSIMISTIC = 2
      LOCK_OPTIMISTIC = 3
      LOCK_BATCH_OPTIMISTIC = 4
  
      # ADODB RecordSet CommandTypeEnum values
      CMD_UNSPECIFIED = -1
      CMD_TEXT = 1
      CMD_TABLE = 2
      CMD_STORED_PROC = 4
      CMD_UNKNOWN = 8
      CMD_FILE = 256
      CMD_TABLE_DIRECT = 512
  
      # ADODB Connection SchemaEnum values
      SCHEMA_COLUMNS = 4
      SCHEMA_TABLES = 20
  
      # ADODB ObjectStateEnum values
      STATE_CLOSED = 0
      STATE_OPEN = 1
  
      # FileService StreamOpenMode values
      STREAM_READ = 1
      STREAM_WRITE = 2
  
      def __init__(self, **kwargs):
          self._log = ''
          self.sascfg = SASConfigCOM(**kwargs)
          self._sb = kwargs.get('sb')
  
          self.pid = self._startsas()
  
      def __del__(self):
          if self.adodb.State == self.STATE_OPEN:
              self._endsas()
  
      def _startsas(self) -> str:
          """
          Create a workspace and open a connection with SAS.
          :return [str]:
          """
          if getattr(self, 'workspace', None) is not None:
              # Do not create a new connection
              return self.workspace.UniqueIdentifier
  
          factory = dynamic.Dispatch('SASObjectManager.ObjectFactoryMulti2')
          server = dynamic.Dispatch('SASObjectManager.ServerDef')
  
          self.keeper = dynamic.Dispatch('SASObjectManager.ObjectKeeper')
          self.adodb = dynamic.Dispatch('ADODB.Connection')
  
          if self.sascfg.host is None:
              # Create a local connection.
              server.MachineDNSName = '127.0.0.1'
              server.Port = 0
              server.Protocol = self.PROTOCOL_COM
  
              user = None
              password = None
          else:
              # Create a remote connection. The following are required:
              #   1. host
              #   2. port
              #   3. class_id
              server.MachineDNSName = self.sascfg.host
              server.Port = self.sascfg.port
              server.Protocol = self.PROTOCOL_IOM
              server.ClassIdentifier = self.sascfg.class_id
  
              if self.sascfg.user is not None:
                  user = self.sascfg.user
              else:
                  user = self.sascfg._prompt('Username: ')
  
              if self.sascfg.pw is not None:
                  password = self.sascfg.pw
              else:
                  password = self.sascfg._prompt('Password: ', pw=True)
  
          self.workspace = factory.CreateObjectByServer(self.SAS_APP, True,
              server, user, password)
  
          self.keeper.AddObject(1, 'WorkspaceObject', self.workspace)
          self.adodb.Open('Provider={}; Data Source=iom-id://{}'.format(
              self.sascfg.provider, self.workspace.UniqueIdentifier))
  
          ll = self.submit("options svgtitle='svgtitle'; options validvarname=any validmemname=extend pagesize=max nosyntaxcheck; ods graphics on;", "text")
          if self.sascfg.verbose:
              print("SAS Connection established. Workspace UniqueIdentifier is "+str(self.workspace.UniqueIdentifier)+"\n")
  
          return self.workspace.UniqueIdentifier
  
      def _endsas(self):
          """
          Close a connection with SAS.
          """
          self.adodb.Close()
          self.keeper.RemoveObject(self.workspace)
          self.workspace.Close()
          if self.sascfg.verbose:
              print("SAS Connection terminated. Workspace UniqueIdentifierid was "+str(self.pid))
  
      def _getlst(self, buf: int=2048) -> str:
          """
          Flush listing.
          :option buf [int]: Download buffer. Default 2048.
          :return [str]:
          """
          flushed = self.workspace.LanguageService.FlushList(buf)
          result = flushed
          while flushed:
              flushed = self.workspace.LanguageService.FlushList(buf)
              result += flushed
  
          return result
  
      def _getlog(self, buf: int=2048) -> str:
          """
          Flush log.
          :option buf [int]: Download buffer. Default 2048.
          :return [str]:
          """
          flushed = self.workspace.LanguageService.FlushLog(buf)
          result = flushed
          while flushed:
              flushed = self.workspace.LanguageService.FlushLog(buf)
              result += flushed
  
          # Store flush result in running log
          self._log += result
  
          if result.count('ERROR:') > 0:
             warnings.warn("Noticed 'ERROR:' in LOG, you ought to take a look and see if there was a problem")
             self._sb.check_error_log = True
  
          return result
  
      def _getfile(self, fname: str, buf: int=2048, decode: bool=False) -> str:
          """
          Use object file service to download a file from the provider.
          :param fname [str]: Filename.
          :option buf [int]: Download buffer. Default 2048.
          :option decode [bool]: Decode the byte stream.
          :return [str]:
          """
          fobj = self.workspace.FileService.AssignFileref('outfile', 'DISK', fname, '', '')
  
          # Use binary stream to support text and image transfers. The binary
          # stream interface does not require a max line length, which allows
          # support of arbitrarily wide tables.
          stream = fobj[0].OpenBinaryStream(self.STREAM_READ)
          flushed = stream.Read(buf)
          result = bytes(flushed)
          while flushed:
              flushed = stream.Read(buf)
              result += bytes(flushed)
  
          stream.Close()
          self.workspace.FileService.DeassignFileref(fobj[0].FilerefName)
  
          if decode is True:
              result = result.decode(self.sascfg.encoding, errors='replace')
  
          return result
  
      def _gethtmlfn(self) -> str:
          """
          Return the path of the output HTML file. This is the combination of
          the `workpath` attribute and `HTML_RESULT_FILE` constant.
          :return [str]:
          """
          return self._sb.workpath + self.HTML_RESULT_FILE
  
      def _reset(self):
          """
          Reset the LanguageService interface to its initial state with respect
          to token scanning. Use it to release the LanguageService from an error
          state associated with the execution of invalid syntax or incomplete
          program source. This primarily occurs when a statement is submitted
          without a trailing semicolon.
          """
          self.workspace.LanguageService.Reset()
  
      def _tablepath(self, table: str, libref: str=None) -> str:
          """
          Define a sas dataset path based on a table name and optional libref
          name. Will return a two-level or one-level path string based on the
          provided arguments. One-level names are of this form: `table`, while
          two-level names are of this form: `libref.table`. If libref is not
          defined, SAS will implicitly define the library to WORK or USER. The
          USER library needs to have been defined previously in SAS, otherwise
          WORK is the default option. If the `libref` parameter is any value
          that evaluates to `False`, the one-level path is returned.
          :param table [str]: SAS data set name.
          :option libref [str]: Optional library name.
          :return [str]:
          """
          if not libref:
              path = "'{}'n".format(table.strip())
          else:
              path = "{}.'{}'n".format(libref, table.strip())
  
          return path
  
      def _schema(self, table: str, libref: str=None) -> dict:
          """
          Request a table schema for a given `libref.table`.
          :param table [str]: Table name
          :option libref [str]: Library name.
          :return [dict]:
          """
          #tablepath = self._tablepath(table, libref=libref)
          if not libref:
              tablepath = table
          else:
              tablepath = "{}.{}".format(libref, table)
  
          criteria = [None, None, tablepath]
  
          schema = self.adodb.OpenSchema(self.SCHEMA_COLUMNS, criteria)
          schema.MoveFirst()
  
          metadata = {}
          while not schema.EOF:
              col_info = {x.Name: x.Value for x in schema.Fields}
              if col_info['FORMAT_NAME'] in self._sb.sas_date_fmts:
                  col_info['CONVERT'] = lambda x: self._sb.SAS_EPOCH + datetime.timedelta(days=x)    if x else x
              elif col_info['FORMAT_NAME'] in self._sb.sas_datetime_fmts:
                  col_info['CONVERT'] = lambda x: self._sb.SAS_EPOCH + datetime.timedelta(seconds=x) if x else x
              # elif FIXME TIME FORMATS
              else:
                  col_info['CONVERT'] = lambda x: x
  
              metadata[col_info['COLUMN_NAME']] = col_info
              schema.MoveNext()
  
          schema.Close()
  
          return metadata
  
      def _prompt(self, key: str, hide: bool=False) -> tuple:
          """
          Ask the user for input about a given key.
          :param key [str]: Key name.
          :option hide [bool]: Hide user keyboard input.
          :return [tuple]:
          """
          input_ok = False
          while input_ok is False:
              val = self.sascfg._prompt('Enter value for macro variable {} '.format(key), pw=hide)
  
              if val is None:
                 raise RuntimeError("No value for prompted macro variable provided.") 
  
              if val:
                  input_ok = True
              else:
                  print('Input not valid.')
  
          return (key, val)
  
      def _asubmit(self, code: str, results: str='html'):
          """
          Submit any SAS code. Does not return a result.
          :param code [str]: SAS statements to execute.
          """
          # Support html ods
          if results.lower() == 'html':
              ods_open = """
                  ods listing close;
                  ods {} (id=saspy_internal) options(bitmap_mode='inline')
                      file="{}"
                      device=svg
                      style={};
                  ods graphics on / outputfmt=png;
              """.format(self.sascfg.output, self._gethtmlfn(), self._sb.HTML_Style)
  
              ods_close = """
                  ods {} (id=saspy_internal) close;
                  ods listing;
              """.format(self.sascfg.output)
          else:
              ods_open = ''
              ods_close = ''
  
          # Submit program
          full_code = ods_open + code + ods_close
          self.workspace.LanguageService.Submit(full_code)
  
      def submit(self, code: str, results: str='html', prompt: dict=None, **kwargs) -> dict:
          """
          Submit any SAS code. Returns log and listing as dictionary with keys
          LOG and LST.
          :param code [str]: SAS statements to execute.
          :option results [str]: Result format. Options: HTML, TEXT. Default HTML.
          :option prompt [dict]: Create macro variables from prompted keys.
          """
          RESET   = """;*';*";*/;quit;run;"""
          prompt  = prompt if prompt is not None else {}
          printto = kwargs.pop('undo', False)
  
          macro_declare = ''
          for key, value in prompt.items():
              macro_declare += '%let {} = {};\n'.format(*self._prompt(key, value))
  
          # Submit program
          self._asubmit(RESET + macro_declare + code + RESET, results)
  
          # Retrieve listing and log
          log = self._getlog()
          if results.lower() == 'html':
              # Make the following replacements in HTML listing:
              #   1. Swap \x0c for \n
              #   2. Change body class selector
              #   3. Increase font size
              listing = self._getfile(self._gethtmlfn(), decode=True) \
                  .replace(chr(12), chr(10)) \
                  .replace('<body class="c body">', '<body class="l body">') \
                  .replace('font-size: x-small;', 'font-size: normal;')
          else:
              listing = self._getlst()
  
          # Invalid syntax will put the interface in to an error state. Reset
          # the LanguageService to prevent further errors.
          # FIXME: In the future, may only want to reset on ERROR. However, this
          # operation seems pretty lightweight, so calling `_reset()` on all
          # submits is not a burden.
          self._reset()
  
          if printto:
             self._asubmit("\nproc printto;run;\n", 'text')
             log += self._getlog()
  
          self._sb._lastlog = log
          return {'LOG': log, 'LST': listing}
  
      def saslog(self) -> str:
          """
          Return the full SAS log.
          :return [str]:
          """
          return self._log
  
      def exist(self, table: str, libref: str=None) -> bool:
          """
          Determine if a `libref.table` exists.
          :param table [str]: Table name
          :option libref [str]: Library name.
          :return [bool]:
          """
          #tablepath = self._tablepath(table, libref=libref)
          #criteria = [None, None, tablepath]
  
          #schema = self.adodb.OpenSchema(self.SCHEMA_COLUMNS, criteria)
          #exists = not schema.BOF
  
          #schema.Close()
  
          #return exists
  
          code  = 'data _null_; e = exist("'
          if len(libref):
             code += libref+"."
          code += "'"+table.strip()+"'n"+'"'+");\n"
          code += 'v = exist("'
          if len(libref):
             code += libref+"."
          code += "'"+table.strip()+"'n"+'"'+", 'VIEW');\n if e or v then e = 1;\n"
          code += "te='TABLE_EXISTS='; put te e;run;\n"
  
          ll = self.submit(code, "text")
        
          l2 = ll['LOG'].rpartition("TABLE_EXISTS= ")
          l2 = l2[2].partition("\n")
          exists = int(l2[0])
        
          return bool(exists)
  
  
      def read_sasdata(self, table: str, libref: str=None, dsopts: dict=None) -> tuple:
          """
          Read any SAS dataset and return as a tuple of header, rows
          :param table [str]: Table name
          :option libref [str]: Library name.
          :option dsopts [dict]: Dataset options.
          :return [tuple]:
          """
          TARGET = '_saspy_sd2df'
          EXPORT = """
              data {tgt};
                  set {tbl} {dopt};
              run;
          """
  
          dsopts = self._sb._dsopts(dsopts) if dsopts is not None else ''
          tablepath = self._tablepath(table, libref=libref)
          recordset = dynamic.Dispatch('ADODB.RecordSet')
  
          # Create an intermediate dataset with `dsopts` applied
          export = EXPORT.format(tgt=TARGET, tbl=tablepath, dopt=dsopts)
          self.workspace.LanguageService.Submit(export)
          meta = self._schema(TARGET)
  
          # Connect RecordSet object to ADODB connection with params:
          #   Cursor:     Forward Only
          #   Lock:       Read Only
          #   Command:    Table Direct
          recordset.Open(TARGET, self.adodb, self.CURSOR_FORWARD,
              self.LOCK_READONLY, self.CMD_TABLE_DIRECT)
          recordset.MoveFirst()
  
          header = [x.Name for x in recordset.Fields]
          rows = []
          while not recordset.EOF:
              rows.append([meta[x.Name]['CONVERT'](x.Value) for x in recordset.Fields])
              recordset.MoveNext()
  
          recordset.Close()
  
          return (header, rows, meta)
  
      def read_csv(self, filepath: str, table: str, libref: str=None, nosub: bool=False, opts: dict=None):
          """
          Submit an import job to the SAS workspace.
          :param filepath [str]: File URI.
          :param table [str]: Table name.
          :option libref [str]: Library name.
          :option nosob [bool]: Return the SAS code instead of executing it.
          :option opts [dict]: SAS PROC IMPORT options.
          """
          opts = opts if opts is not None else {}
          filepath = 'url ' + filepath if filepath.lower().startswith('http') else filepath
          tablepath = self._tablepath(table, libref=libref)
  
          proc_code = """
              filename csv_file "{}";
              proc import datafile=csv_file out={} dbms=csv replace;
                  {}
              run;
          """.format(filepath.replace('"', '""'), tablepath, self._sb._impopts(opts))
  
          if nosub is True:
              return proc_code
          else:
              return self.submit(proc_code, 'text')
  
      def write_csv(self, filepath: str, table: str, libref: str=None, nosub: bool=True, dsopts: dict=None, opts: dict=None):
          """
          Submit an export job to the SAS workspace.
          :param filepath [str]: File URI.
          :param table [str]: Table name.
          :option libref [str]: Library name.
          :option nosob [bool]: Return the SAS code instead of executing it.
          :option opts [dict]: SAS PROC IMPORT options.
          :option dsopts [dict]: SAS dataset options.
          """
          opts = opts if opts is not None else {}
          dsopts = dsopts if dsopts is not None else {}
          tablepath = self._tablepath(table, libref=libref)
  
          proc_code = """
              filename csv_file "{}";
              proc export data={} {} outfile=csv_file dbms=csv replace;
                  {}
              run;
          """.format(filepath.replace('"', '""'), tablepath, self._sb._dsopts(dsopts), self._sb._expopts(opts))
  
          if nosub is True:
              return proc_code
          else:
              return self.submit(proc_code, 'text')['LOG']
  
      def dataframe2sasdata(self, df: '<Pandas Data Frame object>', table: str ='a',
                            libref: str ="", keep_outer_quotes: bool=False,
                                             embedded_newlines: bool=True,
                            LF: str = '\x01', CR: str = '\x02',
                            colsep: str = '\x03', colrep: str = ' ',
                            datetimes: dict={}, outfmts: dict={}, labels: dict={},
                            outdsopts: dict={}, encode_errors = None, char_lengths = None,
                            **kwargs):
          """
          Create a SAS dataset from a pandas data frame.
          :param df [pd.DataFrame]: Pandas data frame containing data to write.
          :param table [str]: Table name.
          :option libref [str]: Library name. Default work.
  
          None of these options are used by this access method; they are needed for other access methods
          keep_outer_quotes - for character columns, have SAS keep any outer quotes instead of stripping them off.
          embedded_newlines - if any char columns have embedded CR or LF, set this to True to get them iported into the SAS data set
          LF - if embedded_newlines=True, the chacter to use for LF when transferring the data; defaults to '\x01'
          CR - if embedded_newlines=True, the chacter to use for CR when transferring the data; defaults to '\x02'
          colsep - the column seperator character used for streaming the delimmited data to SAS defaults to '\x03'
          colrep - the char to convert to for any embedded colsep, LF, CR chars in the data; defaults to  ' '
          datetimes - not implemented yet in this access method
          outfmts - not implemented yet in this access method
          labels - not implemented yet in this access method
          outdsopts - not implemented yet in this access method
          encode_errors - not implemented yet in this access method
          char_lengths - not implemented yet in this access method
          """
          DATETIME_NAME = 'DATETIME26.6'
          DATETIME_FMT = '%Y-%m-%dT%H:%M:%S.%f'
  
          if self.sascfg.verbose:
             if keep_outer_quotes != False:
                print("'keep_outer_quotes=' is not used with this access method. option ignored.")
             if embedded_newlines != True:
                print("'embedded_newlines=' is not used with this access method. option ignored.")
             if LF != '\x01' or CR != '\x02' or colsep != '\x03':
                print("'LF=, CR= and colsep=' are not used with this access method. option(s) ignored.")
             if datetimes != {}:
                print("'datetimes=' is not used with this access method. option ignored.")
             if outfmts != {}:
                print("'outfmts=' is not used with this access method. option ignored.")
             if labels != {}:
                print("'labels=' is not used with this access method. option ignored.")
             if outdsopts != {}:
                print("'outdsopts=' is not used with this access method. option ignored.")
             if encode_errors:
                print("'encode_errors=' is not used with this access method. option ignored.")
             if char_lengths:
                print("'char_lengths=' is not used with this access method. option ignored.")
  
          tablepath = self._tablepath(table, libref=libref)
  
          if type(df.index) != pd.RangeIndex:
             warnings.warn("Note that Indexes are not transferred over as columns. Only actual coulmns are transferred")
  
          columns = []
          formats = {}
          for i, name in enumerate(df.columns):
              if df[name].dtypes.kind in self.PD_NUM_TYPE:
                  # Numeric type
                  definition = "'{}'n num".format(name)
                  formats[name] = lambda x: str(x) if pd.isnull(x) is False else 'NULL'
              elif df[name].dtypes.kind in self.PD_STR_TYPE:
                  # Character type
                  # NOTE: If a character string contains a single `'`, replace
                  #       it with `''`. This is the SAS equivalent to `\'`.
                  length = df[name].map(len).max()
                  definition = "'{}'n char({})".format(name, length)
                  formats[name] = lambda x: "'{}'".format(x.replace("'", "''")) if pd.isnull(x) is False else 'NULL'
              elif df[name].dtypes.kind in self.PD_DT_TYPE:
                  # Datetime type
                  definition = "'{}'n num informat={} format={}".format(name, DATETIME_NAME, DATETIME_NAME)
                  formats[name] = lambda x: "'{:{}}'DT".format(x, DATETIME_FMT) if pd.isnull(x) is False else 'NULL'
              else:
                  # Default to character type
                  # NOTE: If a character string contains a single `'`, replace
                  #       it with `''`. This is the SAS equivalent to `\'`.
                  length = df[name].map(str).map(len).max()
                  definition = "'{}'n char({})".format(name, length)
                  formats[name] = lambda x: "'{}'".format(x.replace("'", "''")) if pd.isnull(x) is False else 'NULL'
  
              columns.append(definition)
  
          sql_values = []
          for index, row in df.iterrows():
              vals = []
              for i, col in enumerate(row):
                  func = formats[df.columns[i]]
                  vals.append(func(col))
  
              sql_values.append('values({})'.format(', '.join(vals)))
  
          sql_create = 'create table {} ({});'.format(tablepath, ', '.join(columns))
          sql_insert = 'insert into {} {};'.format(tablepath, '\n'.join(sql_values))
  
          self.adodb.Execute(sql_create)
          self.adodb.Execute(sql_insert)
          return None
  
      def sasdata2dataframe(self, table: str, libref: str=None, dsopts: dict=None, method: str='', **kwargs) -> 'pd.DataFrame':
          """
          Create a pandas data frame from a SAS dataset.
          :param table [str]: Table name.
          :option libref [str]: Library name.
          :option dsopts [dict]: Dataset options.
          :option method [str]: Download method.
          :option tempkeep [bool]: Download the csv file if using the csv method.
          :option tempfile [str]: File path for the saved output file.
          :return [pd.DataFrame]:
          """
          # strip off unused by this access method options from kwargs
          # so they can't be passes to panda later
          rowsep = kwargs.pop('rowsep', ' ')
          colsep = kwargs.pop('colsep', ' ')
          rowrep = kwargs.pop('rowrep', ' ')
          colrep = kwargs.pop('colrep', ' ')
  
          if method.upper() == 'DISK':
             print("This access method doesn't support the DISK method. Try CSV or MEMORY")
             return None
  
          if method.upper() == 'CSV':
              df = self.sasdata2dataframeCSV(table, libref, dsopts=dsopts, **kwargs)
          else:
              my_fmts = kwargs.pop('my_fmts', False)
              k_dts   = kwargs.pop('dtype',   None)
              if self.sascfg.verbose:
                 if my_fmts != False:
                    print("'my_fmts=' is not supported in this access method. option ignored.")
                 if k_dts is not None:
                    print("'dtype=' is only used with the CSV version of this method. option ignored.")
  
              header, rows, meta = self.read_sasdata(table, libref, dsopts=dsopts)
              df = pd.DataFrame.from_records(rows, columns=header, **kwargs)
  
              for col in meta.keys():
                 if meta[col]['FORMAT_NAME'] in self._sb.sas_date_fmts + self._sb.sas_datetime_fmts: 
                    df[col] = pd.to_datetime(df[col], errors='coerce')
                 elif meta[col]['DATA_TYPE'] == 5:
                    df[col] = pd.to_numeric(df[col], errors='coerce')
  
          return df
  
      def sasdata2dataframeCSV(self, table: str, libref: str ='', dsopts: dict = None, 
                               tempfile: str=None, tempkeep: bool=False, **kwargs) -> 'pd.DataFrame':
          """
          Create a pandas data frame from a SAS dataset.
          :param table [str]: Table name.
          :option libref [str]: Library name.
          :option dsopts [dict]: Dataset options.
          :option opts [dict]: dictionary containing any of the following Proc Export options(delimiter, putnames)
          :option tempkeep [bool]: Download the csv file if using the csv method.
          :option tempfile [str]: File path for the saved output file.
          :return [pd.DataFrame]:
          """
          FORMAT_STRING = '{column} {format}{length}.{precision}'
          EXPORT = """
              data _saspy_sd2df;
                  format {fmt};
                  set {tbl};
              run;
  
              proc export data=_saspy_sd2df {dopt}
                      outfile="{out}"
                      dbms=csv replace;
                      {exopts}
              run;
          """
          k_dts   = kwargs.get('dtype',   None)
          my_fmts = kwargs.pop('my_fmts', False)
          if self.sascfg.verbose:
             if my_fmts != False:
                print("'my_fmts=' is not supported in this access method. option ignored.")
  
          sas_csv = '{}saspy_sd2df.csv'.format(self._sb.workpath)
          dopts = self._sb._dsopts(dsopts) if dsopts is not None else ''
          tablepath = self._tablepath(table, libref=libref)
  
          expopts = self._sb._expopts(kwargs.pop('opts', {}))
  
          # Convert any date format to one pandas can understand (ISO-8601).
          # Save a reference of the column name in a list so pandas can parse
          # the column during construction.
          datecols = []
          fmtlist = []
          meta = self._schema(table, libref)
          for name, col in meta.items():
              if col['FORMAT_NAME'] in self._sb.sas_date_fmts:
                  datecols.append(name)
                  col_format = self.FMT_DEFAULT_DATE_NAME
                  col_length = self.FMT_DEFAULT_DATE_LENGTH
                  col_precis = self.FMT_DEFAULT_DATE_PRECISION
              elif col['FORMAT_NAME'] in self._sb.sas_datetime_fmts:
                  datecols.append(name)
                  col_format = self.FMT_DEFAULT_DATETIME_NAME
                  col_length = self.FMT_DEFAULT_DATETIME_LENGTH
                  col_precis = self.FMT_DEFAULT_DATETIME_PRECISION
              # elif FIXME TIME FORMATS
              else:
                  col_format = col['FORMAT_NAME']
                  col_length = col['FORMAT_LENGTH']
                  col_precis = col['FORMAT_DECIMAL']
  
              if col['FORMAT_NAME']:
                 full_format = FORMAT_STRING.format(
                     column=col['COLUMN_NAME'],
                     format=col_format,
                     length=col_length,
                     precision=col_precis)
     
                 fmtlist.append(full_format)
  
          export = EXPORT.format(fmt=' '.join(fmtlist),
              tbl=tablepath,
              dopt=dopts,
              exopts=expopts,
              out=sas_csv)
  
          # Use `LanguageService.Submit` instead of `submit` for a slight
          # performance bump. We don't need the log or listing here so skip
          # the wrapper function.
          self.workspace.LanguageService.Submit(export)
  
          outstring = self._getfile(sas_csv, decode=True)
  
          # Write temp file if requested by user
          if kwargs.get('tempkeep') is True and kwargs.get('tempfile') is not None:
              with open(kwargs['tempfile'], 'w') as f:
                  f.write(outstring)
  
          df = pd.read_csv(io.StringIO(outstring), parse_dates=datecols, **kwargs)
  
          if k_dts is None:  # don't override these if user provided their own dtypes
             for col in meta.keys():
                if meta[col]['FORMAT_NAME'] in self._sb.sas_date_fmts + self._sb.sas_datetime_fmts: 
                   df[col] = pd.to_datetime(df[col], errors='coerce')
  
          return df
  
      def upload(self, local: str, remote: str, overwrite: bool=True, permission: str='', **kwargs):
          """
          Upload a file to the SAS server.
          :param local [str]: Local filename.
          :param remote [str]: Local filename.
          :option overwrite [bool]: Overwrite the file if it exists.
          :option permission [str]: See SAS filename statement documentation.
          """
          perms = "PERMISSION='{}'".format(permission) if permission else ''
          valid = self._sb.file_info(remote, quiet=True)
  
          if valid == {}:
              # Parameter `remote` references a directory. Default to using the
              # filename in `local` path.
              remote_file = remote + self._sb.hostsep + os.path.basename(local)
          elif valid is not None and overwrite is False:
              # Parameter `remote` references a file that exists but we cannot
              # overwrite it.
              # TODO: Raise exception here instead of returning dict
              return {'Success': False,
                  'LOG': 'File {} exists and overwrite was set to False. Upload was stopped.'.format(remote)}
          else:
              remote_file = remote
  
          with open(local, 'rb') as f:
              fobj = self.workspace.FileService.AssignFileref('infile', 'DISK', remote_file, perms, '')
              stream = fobj[0].OpenBinaryStream(self.STREAM_WRITE)
  
              stream.Write(f.read())
              stream.Close()
              self.workspace.FileService.DeassignFileref(fobj[0].FilerefName)
  
          return {'Success': True,
              'LOG': 'File successfully written using FileService.'}
  
      def download(self, local: str, remote: str, overwrite: bool=True, **kwargs):
          """
          Download a file from the SAS server.
          :param local [str]: Local filename.
          :param remote [str]: Local filename.
          :option overwrite [bool]: Overwrite the file if it exists.
          """
          valid = self._sb.file_info(remote, quiet=True)
  
          if valid is None:
              # Parameter `remote` references an invalid file path.
              # TODO: Raise exception here instead of returning dict
              return {'Success': False,
                  'LOG': 'File {} does not exist.'.format(remote)}
          elif valid == {}:
              # Parameter `remote` references a directory.
              # TODO: Raise exception here instead of returning dict
              return {'Success': False,
                  'LOG': 'File {} is a directory.'.format(remote)}
          
          if os.path.isdir(local) is True:
              # Parameter `local` references a directory. Default to using the
              # filename in `remote` path.
              local_file = os.path.join(local, remote.rpartition(self._sb.hostsep)[2])
          else:
              local_file = local
  
          with open(local_file, 'wb') as f:
              f.write(self._getfile(remote))
  
          return {'Success': True,
              'LOG': 'File successfully read using FileService.'}
  