Tables Action Set: Examples

Provides actions for accessing and managing data

Use a SAS Informat with a Delimited File

SAS informats are useful for interpreting a mix of alphanumeric characters as a numeric value. Unless you use a SAS informat, loading the following data into CAS results in VARCHAR columns for RecordDt, StartDt, and EndDt.

Note: The specified path in this example ('/path/to/myusage.csv') is for a Linux client. For a Microsoft Windows client, specify a path value such as 'C:\path\to\myusage.csv' or a UNC path such as '\\host\path\to\myusage.csv'.

account,high,low,recorddt,startdt,enddt,kwh
1000001,82,71,07/03/2013,07/01/2013,07/02/2013,54
1000001,85,70,07/02/2013,06/30/2013,07/01/2013,70
1000001,85,68,07/01/2013,06/29/2013,06/30/2013,78
1000001,90,70,06/30/2013,06/28/2013,06/29/2013,72
1000001,88,68,06/29/2013,06/27/2013,06/28/2013,54

The following example shows the UPLOAD statement. However, you can also use informats when you specify the importOptions parameter with the loadTable action.

/* Specify a host and port that are valid for your site.*/
*options cashost="cloud.example.com" casport=5570;  

/* If not already done, start session Casauto. */ 
*cas casauto;

proc cas;
   upload path="/path/to/myusage.csv"                             /* 1 */
          casout={name="myusage", replace=True}
          importOptions={
             fileType="csv",
             getNames=True,
             vars={                                               /* 2 */
                recorddt={informat="mmddyy10.", format="nldate20."}
                startdt={informat="mmddyy10.", format="nldate20."}
                enddt={informat="mmddyy10.", format="nldate20."}
            }};

   table.fetch / table="myusage" to=5;
run;

The UPLOAD statement is used to transfer the myusage.csv file from a path that is accessible to SAS to the server.
The vars parameter is used to specify column-specific information. In this example, the delimited file includes column names. Because the getNames parameter was set to True, the columns can be identified as keys. The MMDDYY10 informat is applied to read the dates in a format that is common in the United States of America. The text is converted to a numeric value, a DOUBLE, in the in-memory table. A locale-aware format, NLDATE20 is applied to the same columns.

The MMDDYY10. informat converts the date as text to a DOUBLE. The value of the double is the number of days since January 1, 1960. For more information, see the MMDDYYw. informat.

The results from the fetch action show dates such as July 03, 2013, July 02, 2013, and so on.

account,high,low,recorddt,startdt,enddt,kwh
1000001,82,71,07/03/2013,07/01/2013,07/02/2013,54
1000001,85,70,07/02/2013,06/30/2013,07/01/2013,70
1000001,85,68,07/01/2013,06/29/2013,06/30/2013,78
1000001,90,70,06/30/2013,06/28/2013,06/29/2013,72
1000001,88,68,06/29/2013,06/27/2013,06/28/2013,54

The following example shows the upload_file method that is part of SWAT. However, you can also use informats when you specify the importOptions parameter with the loadTable action.

import swat

# change the host and port to match your site
s = swat.CAS("cloud.example.com", 5570)

myusage = s.CASTable("myusage", replace=True)

s.upload_file("/path/to/myusage.csv",                             # 1
              casout=myusage, 
              importoptions={
                  "fileType":"csv",
                  "getNames":True,
                  "vars":{                                        # 2
                      "recorddt":{"informat":"mmddyy10.", "format":"nldate20."},
                      "startdt":{"informat":"mmddyy10.", "format":"nldate20."},
                      "enddt":{"informat":"mmddyy10.", "format":"nldate20."}
                  }
              })


myusage.head(1)                                                   # 3

myusage.table.fetch(to=5, format=True)                            # 4

The upload_file method is used to transfer the myusage.csv file from a path that is accessible to Python to the server. The upload_file method is provided by the SWAT package.
The vars parameter is used to specify column-specific information. In this example, the delimited file includes column names. Because the getNames parameter was set to True, the columns can be identified as keys. The MMDDYY10 informat is applied to read the dates in a format that is common in the United States of America. The text is converted to a numeric value, a DOUBLE, in the in-memory table. A locale-aware format, NLDATE20 is applied to the same columns.

The MMDDYY10. informat converts the date as text to a DOUBLE. The value of the double is the number of days since January 1, 1960. For more information, see the MMDDYYw. informat.
The head method returns the raw numeric values for the date-related columns.
To view the formatted values, you can use the fetch action and set the format parameter to True. In this case, the server formats the numeric values to locale-aware strings and returns the strings.

The head method returns numeric values for the date-related columns.

The results from the fetch action show dates such as July 03, 2013, July 02, 2013, and so on.

account,high,low,recorddt,startdt,enddt,kwh
1000001,82,71,07/03/2013,07/01/2013,07/02/2013,54
1000001,85,70,07/02/2013,06/30/2013,07/01/2013,70
1000001,85,68,07/01/2013,06/29/2013,06/30/2013,78
1000001,90,70,06/30/2013,06/28/2013,06/29/2013,72
1000001,88,68,06/29/2013,06/27/2013,06/28/2013,54

The following example shows the cas.upload.file function that is introduced with R-SWAT version 1.2. However, you can also use informats when you specify the importOptions parameter with the loadTable action.

library(swat)  

# change the host and port to match your site
s <- CAS("cloud.example.com", 5570)

myusage <- cas.upload.file(s,                                     # 1
                           '/path/to/myusage.csv',
                           casout=list(name="myusage", replace=TRUE),
                           importoptions=list(
                             filetype="csv",
                             getNames=TRUE,
                             vars=list(                           # 2
                               recorddt=list(informat="mmddyy10.", format="nldate."),
                               startdt=list(informat="mmddyy10.", format="nldate."),
                               enddt=list(informat="mmddyy10.", format="nldate.")
                             )
                           ))

head(myusage, n=1)                                                # 3

five <- cas.table.fetch(myusage, to=5, format=TRUE)$Fetch         # 4
View(to.data.frame(five))

The cas.upload.file function is used to transfer the myusage.csv file from a path that is accessible to R to the server. The function is provided by the 1.2 release of the R-SWAT package.
The vars parameter is used to specify column-specific information. In this example, the delimited file includes column names. Because the getNames parameter was set to True, the columns can be identified as keys. The MMDDYY10 informat is applied to read the dates in a format that is common in the United States of America. The text is converted to a numeric value, a DOUBLE, in the in-memory table. A locale-aware format, NLDATE20 is applied to the same columns.

The MMDDYY10. informat converts the date as text to a DOUBLE. The value of the double is the number of days since January 1, 1960. For more information, see the MMDDYYw. informat.
The head method that is provided by the R-SWAT package returns the raw numeric values for the date-related columns.
To view the formatted values, you can use the fetch action and set the format parameter to True. In this case, the server formats the numeric values to locale-aware strings and returns the strings.

The head method returns numeric values for the date-related columns.

  account high low recorddt startdt enddt kwh
1 1000001   82  71    19542   19540 19541  54

The results from the fetch action show dates such as July 03, 2013, July 02, 2013, and so on.

account,high,low,recorddt,startdt,enddt,kwh
1000001,82,71,07/03/2013,07/01/2013,07/02/2013,54
1000001,85,70,07/02/2013,06/30/2013,07/01/2013,70
1000001,85,68,07/01/2013,06/29/2013,06/30/2013,78
1000001,90,70,06/30/2013,06/28/2013,06/29/2013,72
1000001,88,68,06/29/2013,06/27/2013,06/28/2013,54

The following example shows the upload function that is provided by the Lua SWAT package. However, you can also use informats when you specify the importOptions parameter with the loadTable action.

swat = require 'swat'  

-- change the host and port to match your site
s = swat.CAS{"cloud.example.com", 5570}

s:upload{'/path/to/myusage.csv',                                  -- 1
         casout={name="myusage", replace=true},
         importoptions={
           filetype="csv",
           getNames=true,
           vars={                                                 -- 2
             recorddt={informat="mmddyy10.", format="nldate."},
             startdt={informat="mmddyy10.", format="nldate."},
             enddt={informat="mmddyy10.", format="nldate."}
           }
         }}

s:table_fetch{table="myusage", to=5, format=true}                 -- 3

The upload function is used to transfer the myusage.csv file from a path that is accessible to Lua to the server. The function is provided by the Lua SWAT package.
The vars parameter is used to specify column-specific information. In this example, the delimited file includes column names. Because the getNames parameter was set to True, the columns can be identified as keys. The MMDDYY10 informat is applied to read the dates in a format that is common in the United States of America. The text is converted to a numeric value, a DOUBLE, in the in-memory table. A locale-aware format, NLDATE20 is applied to the same columns.

The MMDDYY10. informat converts the date as text to a DOUBLE. The value of the double is the number of days since January 1, 1960. For more information, see the MMDDYYw. informat.
To view the formatted values, you can use the fetch action and set the format parameter to True. In this case, the server formats the numeric values to locale-aware strings and returns the strings.

The results from the fetch action show dates such as July 03, 2013, July 02, 2013, and so on.

[Fetch]

                Selected Rows from Table MYUSAGE
_Index_  account       high          low           recorddt
      1       1000001            82            71  July 03, 2013
      2       1000001            85            70  July 02, 2013
      3       1000001            85            68  July 01, 2013
      4       1000001            90            70  June 30, 2013
      5       1000001            88            68  June 29, 2013

                Selected Rows from Table MYUSAGE
startdt        enddt          kwh
July 01, 2013  July 02, 2013            54
June 30, 2013  July 01, 2013            70
June 29, 2013  June 30, 2013            78
June 28, 2013  June 29, 2013            72
June 27, 2013  June 28, 2013            54

Last updated: October 6, 2022