how to read SAS table

Hi, all:
I am mainly using SAS to get raw data and generate SAS tables for further analysis. Can anyone tell me how I can let KNIME read SAS table? Thank you so much for your help.

So far there is no possibility to read in SAS Tables directly (though we would be happy to provide such a node). However, you can export the data to csv file and then import it to KNIME:

  • Within SAS use the wizard under File -> Export data.
  • The referring code would be
    libname sasdata "C:/SASImport";
    proc export data=sasdata.importtest
            outfile= "importtest.csv"
            DBMS=CSV REPLACE;
    
  • The import is then accomplished by using the File Reader node.

Or you may try to get the raw data directly into KNIME using the File Reader node or the Database Reader node :wink:

Please let us know if this works for you since we were not able to test the proposed solution.

Hi, Fabian:

I exported one SAS table that contains over 4 millions of records with 9 variables into CSV file and read it somehow sucessfully into KNIME. But it was not totally sucessful because of two problems:

(1) One variable, OrderDate, is a numeric variable in SAS but KNIME reconized it as string type of variable. However, I need it to be of integer type just like in SAS because I may need to sort my data by OrderDate. For example, how would I know what was the earliest date that a specific customer ordered from us?
(2) Another variable, SalesAmount, is a numeric variable with double real type in SAS but again was reconized by KNIME as string variable. I tried to changed it to Double type in KNIME but an error occured because one record has SalesAmount=1,120.48. Was it because of the comma that made KNIME not able to change the type from string to double?

Thanks a lot for your help.

  1. In the File Reader dialog you can click on the column header and manually change the type of the column in the opened dialog: You may also have a look at the Advanced... dialog, where you can set the decimal and thousands delimiters. With this option you should be able to convert the OrderDate into an integer. However, if you just want to sort your data: the Sorter node would also sort the string column correctly.
  2. The File Reader tries to guess the right type for your data. If a column is of type string it was not possible to convert it into double with the default settings. There are 2 possibilities in KNIME to convert data to double:
    1. with the File Reader node by changing the column type and the delimiters as described above or
    2. by using the String to Number node
    However, in both cases the delimiters must be the same for the whole column (e.g. ',' as a thousands delimiter and '.' as decimal delimiter) - otherwise you get strange numbers out of the sudden ;-) How did you tried to convert the data into type double?

Another possibility could be using the SAS JDBC drivers and/ or the SAS OLEDB Provider, however I did not try that out yet since I use another program for doing the data manipulation and then export from it the content in csv format…

Just wanted to make sure this was updated for all!

As of version 2.4, KNIME can now read SAS datasets (sometimes called tables) in the SASB7DAT format via the SASB7DAT node.   This node is available via the KNIME Labs Extensions, which you can get via the INSTALL File Extensions link on the FILE pulldown.   There are full instructions (pretty trivial really:  point to the file and click) but also information about which dataset types are supported.