tz_sas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "28ee0c25-630e-4c05-8cfa-bca9f320cf1b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-01-11T22:32:23.096368Z",
     "iopub.status.busy": "2024-01-11T22:32:23.096119Z",
     "iopub.status.idle": "2024-01-11T22:32:23.610279Z",
     "shell.execute_reply": "2024-01-11T22:32:23.610022Z",
     "shell.execute_reply.started": "2024-01-11T22:32:23.096332Z"
    }
   },
   "outputs": [],
   "source": [
    "from wrds2pg import get_process"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "02bd5cdc-1832-4478-902d-134bf70972d4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-01-11T22:32:23.611563Z",
     "iopub.status.busy": "2024-01-11T22:32:23.611378Z",
     "iopub.status.idle": "2024-01-11T22:32:23.614931Z",
     "shell.execute_reply": "2024-01-11T22:32:23.614558Z",
     "shell.execute_reply.started": "2024-01-11T22:32:23.611546Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        temp_var_0 = input(file_accepted, YMDDTTM.);\n",
      "        drop file_accepted;\n",
      "        rename temp_var_0 = file_accepted;\n",
      "\n",
      "        temp_var_1 = input(ac_file_accepted, YMDDTTM.);\n",
      "        drop ac_file_accepted;\n",
      "        rename temp_var_1 = ac_file_accepted;\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tz = \"America/New_York\"\n",
    "schema = \"audit\"\n",
    "table_name = \"feed20_nt\"\n",
    "obs = 20\n",
    "\n",
    "tz_vars = [\"file_accepted\", \"ac_file_accepted\"]\n",
    "keep = \"nt_notify_key \" + ' '.join(tz_vars)\n",
    "def tz_str(i, var):\n",
    "    temp =  f\"        temp_var_{i} = input({var}, YMDDTTM.);\\n\"\n",
    "    temp += f\"        drop {var};\\n\"\n",
    "    temp += f\"        rename temp_var_{i} = {var};\\n\"\n",
    "    return temp\n",
    "\n",
    "if tz_vars:\n",
    "    tz_str_list = [tz_str(i, var) for i, var in enumerate(tz_vars)]\n",
    "    tz_str = '\\n'.join(tz_str_list)\n",
    "    timestamps_list = [ '        attrib ' + var + ' format=E8601DX.;'\n",
    "                                       for var in tz_vars]\n",
    "    timestamps_str = '\\n'.join(timestamps_list)\n",
    "\n",
    "print(tz_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c2a757e5-dd7f-4b22-b47a-3c5a1080dda4",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-01-11T22:32:23.615483Z",
     "iopub.status.busy": "2024-01-11T22:32:23.615364Z",
     "iopub.status.idle": "2024-01-11T22:32:23.617602Z",
     "shell.execute_reply": "2024-01-11T22:32:23.617332Z",
     "shell.execute_reply.started": "2024-01-11T22:32:23.615476Z"
    }
   },
   "outputs": [],
   "source": [
    "if obs:\n",
    "    obs_str = f\"obs={obs}\"\n",
    "\n",
    "if keep:\n",
    "    keep_str = f\"keep={keep}\"\n",
    "\n",
    "sas_code = f\"\"\"\n",
    "    options nosource nonotes;\n",
    "    options timezone='{tz}';\n",
    "    \n",
    "    data temp;\n",
    "        set {schema}.{table_name}({obs_str} {keep_str});\n",
    "{tz_str}\n",
    "    run;\n",
    "\n",
    "    proc datasets lib=work;\n",
    "        modify temp;\n",
    "{timestamps_str}\n",
    "    run;\n",
    "\n",
    "    proc export data=temp\n",
    "        outfile=stdout \n",
    "        dbms=csv;\n",
    "    run;\n",
    "    \n",
    "    /*\n",
    "    proc contents data=temp;\n",
    "    run;\n",
    "    */\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "400bb8da-64fa-4854-98e9-5a17d79134ed",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-01-11T22:32:23.618159Z",
     "iopub.status.busy": "2024-01-11T22:32:23.618049Z",
     "iopub.status.idle": "2024-01-11T22:32:23.620122Z",
     "shell.execute_reply": "2024-01-11T22:32:23.619811Z",
     "shell.execute_reply.started": "2024-01-11T22:32:23.618147Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    options nosource nonotes;\n",
      "    options timezone='America/New_York';\n",
      "    \n",
      "    data temp;\n",
      "        set audit.feed20_nt(obs=20 keep=nt_notify_key file_accepted ac_file_accepted);\n",
      "        temp_var_0 = input(file_accepted, YMDDTTM.);\n",
      "        drop file_accepted;\n",
      "        rename temp_var_0 = file_accepted;\n",
      "\n",
      "        temp_var_1 = input(ac_file_accepted, YMDDTTM.);\n",
      "        drop ac_file_accepted;\n",
      "        rename temp_var_1 = ac_file_accepted;\n",
      "\n",
      "    run;\n",
      "\n",
      "    proc datasets lib=work;\n",
      "        modify temp;\n",
      "        attrib file_accepted format=E8601DX.;\n",
      "        attrib ac_file_accepted format=E8601DX.;\n",
      "    run;\n",
      "\n",
      "    proc export data=temp\n",
      "        outfile=stdout \n",
      "        dbms=csv;\n",
      "    run;\n",
      "    \n",
      "    /*\n",
      "    proc contents data=temp;\n",
      "    run;\n",
      "    */\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(sas_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3b34700b-0f51-4d08-b6c1-acabe79a9ed6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-01-11T22:32:23.620768Z",
     "iopub.status.busy": "2024-01-11T22:32:23.620670Z",
     "iopub.status.idle": "2024-01-11T22:32:24.496617Z",
     "shell.execute_reply": "2024-01-11T22:32:24.494923Z",
     "shell.execute_reply.started": "2024-01-11T22:32:23.620760Z"
    }
   },
   "outputs": [],
   "source": [
    "p = get_process(sas_code, wrds_id=\"iangow\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "764d5a6c-c7e7-44fa-b03c-f8ffad132a3c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-01-11T22:32:24.500041Z",
     "iopub.status.busy": "2024-01-11T22:32:24.498739Z",
     "iopub.status.idle": "2024-01-11T22:32:28.090700Z",
     "shell.execute_reply": "2024-01-11T22:32:28.087823Z",
     "shell.execute_reply.started": "2024-01-11T22:32:24.500002Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['NT_NOTIFY_KEY,file_accepted,ac_file_accepted\\n',\n",
       " '18350,2004-05-17T13:19:23-04:00,2004-05-24T13:33:35-04:00\\n',\n",
       " '15476,2004-08-16T15:01:27-04:00,2004-08-18T11:56:04-04:00\\n',\n",
       " '4370,2005-04-01T09:39:51-05:00,2005-05-19T05:55:59-04:00\\n',\n",
       " '4246,2005-05-17T10:46:11-04:00,2005-06-23T10:47:44-04:00\\n',\n",
       " '2271,2005-08-16T10:41:29-04:00,2005-09-02T12:52:40-04:00\\n',\n",
       " '754,2005-11-15T11:35:43-05:00,2005-12-02T09:55:40-05:00\\n',\n",
       " '13211,2006-05-16T06:56:34-04:00,2006-05-21T20:00:00-04:00\\n',\n",
       " '26155,2006-08-14T15:27:39-04:00,2006-08-18T12:18:15-04:00\\n',\n",
       " '33292,2006-11-15T06:40:33-05:00,2006-11-20T07:37:08-05:00\\n',\n",
       " '48220,2007-04-03T08:32:27-04:00,2007-04-17T09:45:19-04:00\\n',\n",
       " '53008,2007-05-15T20:00:00-04:00,2007-05-21T07:16:38-04:00\\n',\n",
       " '54900,2007-08-14T20:00:00-04:00,2007-08-20T08:40:14-04:00\\n',\n",
       " '56535,2007-11-15T08:57:11-05:00,2007-12-07T12:48:13-05:00\\n',\n",
       " '58210,2008-03-31T09:40:51-04:00,2008-05-15T10:55:05-04:00\\n',\n",
       " '59638,2008-05-15T09:15:04-04:00,2008-05-19T17:59:09-04:00\\n',\n",
       " '61098,2008-08-14T08:55:18-04:00,2008-08-19T09:30:27-04:00\\n',\n",
       " '64454,2009-03-30T15:38:17-04:00,2009-04-13T10:22:59-04:00\\n',\n",
       " '65708,2009-05-13T08:49:15-04:00,2009-05-20T10:00:56-04:00\\n',\n",
       " '67097,2009-08-07T15:10:13-04:00,2009-08-19T11:58:13-04:00\\n',\n",
       " '68553,2009-11-13T12:58:11-05:00,2009-11-23T07:56:38-05:00\\n']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p.readlines()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}