CODEWITHSUNDEEP
importsasnetezzaenterprise-guide
Joshua Schlichting
Joshua Schlichting

Reputation: 3450

SAS PROC IMPORT not creating OUT dataset as commanded

Situation: I'm importing an xlsx file with PROC IMPORT and wanting to send the data OUT to a new netezza database table.

My issue: SAS appears to run fine, but the log shows a completely different table name was been created with a libref that I'm not using (and this libref is cleared).

LIBNAME abc sasionza server=server database=db port=123 user=user pass=pass;
PROC IMPORT
    OUT = abc.DesiredTableName
    DATAFILE= "my/excelfile/file.xlsx"
    DBMS=xlsx
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
RUN;

This "runs" just fine, or so it appears to. I check the log and I see this:

NOTE: The import data set has 11 observations and 7 variables.
NOTE: xyz.ATableCreatedDaysAgoInAnotherProgram data set was successfully created. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.55 seconds cpu time 0.02 seconds

I thought, hmm, that is weird. libref xyz is actually cleared, so I couldn't possibly use it, and ATableCreatedDaysAgoInAnotherProgram is a tablename used in a completely different SAS E-Guide program I have going on.

Sounds like a memory or cache issue. So, I close all instances of SAS E-Guide and fire up a new one. I created a new program that only has my desired lines (the code listed above).

It runs, and I get the following log as a result:

NOTE: The import data set has 11 observations and 7 variables.
NOTE: WORK._PRODSAVAIL data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time): real time 0.55 seconds cpu time 0.02 seconds

I will note that this is the first time I've actually tried to use PROC IMPORT to send something directly to a netezza table. Up until now, I've always imported files into WORK and worked with them for a bit before inserting them into a table in a database. I thought that maybe this is a SAS limitation I may not be aware of, but the SAS documentation for PROC IMPORT (https://v8doc.sas.com/sashtml/proc/z0308090.htm) says that you can specify a two level name in the OUT statement, so I feel that this should work. If it can't work, then I feel that SAS should error out instead of randomly creating a table name that I'm not even executing in my code.

Summary (tl;dr): Can you PROC IMPORT directly into a netezza database table using a libref? And if you can't, why is my code executing and producing text that isn't even related to what I'm doing?

Thanks, everyone!

Upvotes: 0

Views: 1332

Answers (2)

Joshua Schlichting
Joshua Schlichting

Reputation: 3450

The Solution: A column in the xlsx file being imported had a space in one of the column names... Simply removing the space in the column name and saving the changes to the xlsx file allowed for the PROC IMPORT code above to be executed flawlessly with the desired results being imported into the named netezza table.

NOTE: This fixed my problem, but it does not explain the SAS log showing text executing that wasn't actually in the code to be executed.

Upvotes: 1

Tom
Tom

Reputation: 51566

Sounds like you should report the issue with not getting a working ERROR message to SAS.

To make sure that your SAS/Netezza tables do not have variable names with spaces in them change the setting of the VALIDVARNAME option before running your program. That way PROC IMPORT will convert your column headings in the XLSX file into valid variable names.

options validvarname=v7;
libname out ...... ;
proc import out=out.table replace ...

Upvotes: 0

Related Questions