Proc import with Numeric/Character Variable

[Toni2]

Hi, 

I am trying to import Excel file into SAS and i have some issues. More specifically, one of the Excel columns contains a mixed of number and character/numbers values (see below) . The dataset is quite big with more than 100k raws. I use proc import to import to SAS however, SAS has replaced all the character-number values with dots. ( for example, instead of  20HA in SAS i can see .  (dots)) .

I read an article that for DBMS=EXCEL or EXCELCS i need to use the MIXED=Yes option which i did it but i still get an error that the MIXED statement is not valid or used out of proper order

Below you can see my code and SAS log

11319
11319
11319
11319
11319
11319
20HA
20HA
20HA
20HA
20HA

proc import out = data_raw dbms = excelcs datafile = "C:\Documents\data.xlsx" replace;
sheet = "data";
version = "2002";
server = "servername";
port = 8958;
ServerUser = "&userID";
ServerPass = "&pass";
mixed=YES;
run;

LOG

__________________________________________________________________________

33 mixed=YES;
_____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

34 run;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1c2c Thread 0x1d40 DBC 0x21a84e8 Excel'.

ERROR: Error in the LIBNAME statement.

Connection Failed. See log for details.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

[Kurt_Bremser]

Why do you read a csv file as Excel? If it is a true CSV (comma or semicolon-separated text) file, copy it to your SAS environment (unless it's already available there) and read it with a data step, where you can control how this column is read and converted.

Which type should the column be? If numeric, to which value should 20HA be translated?

[Toni2]

hi, apologies i edited my post and changed the file name to .xlsx. Actually, i also converted it to csv and tried to import using proc import but i still had some issues and decided to post here the issue with the .xlsx.

The file is not in SAS at the moment and i need to write a code to able to import it from a specific location rather than import it manually.

In terms of the type of 20HA i need SAS to see and read them. i don't have a specific preference 

[ballardw]

First thing, Excel has no concept of variable so "variable" properties when using a tool like Proc Import guesses as to variable (column) properties using a very few rows of data. So if the first 20 values in a column look like numbers the procedure guesses that the data is supposed to be numeric because Excel enforces no rules at all.

Use Import with a CSV and set the option Guessingrows=max; to examine all the rows of the file before setting properties.

When you have "issues" the best thing is to copy text from the log showing the source of the issues and paste the log into a text box opened on the forum with the </> icon that appears above the message window. Then describe the "issue" you have.

Note: If you are going to read multiple similar structure files you will be better off to write a data step to read each one with the same variables, types and lengths to avoid problems later when you need to combine data.

[Toni2]

hi, thanks for the advices and sorry for the confusion. I have now tried with an CSV. I saved the Excel file in a CSV and use the code below but i still get some errors. See below an extract from the log.

Please note i have edited/removed some fields due to sensitive data  

proc import out = data_raw dbms = csv datafile = "\c:files\data.csv" replace;
    sheet		= "data";
	version		= "2002";
	server		= "server";
	port		= 8000;
	ServerUser = "servername";
    ServerPass = "&pwin"; 
	Guessingrows=max;
run;

NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
SYMBOLGEN:  Macro variable LWIN resolves to ID
27             sheet		= "data";
               _____
               180
NOTE: The previous statement has been deleted.
28         	version		= "2002";
            _______
            180
SYMBOLGEN:  Macro variable PWIN resolves to {SAS002}3FE19032333A577A1D857AEF45BAD3BA141313DF
29         	server		= "servername";
            ______
            180
NOTE: The previous statement has been deleted.
30         	port		= 8000;
            ____
            180
31         	ServerUser = "serveruser";
            __________
            180
32             ServerPass = "&pwin";
               __________
               180
2                                                          The SAS System                        15:43 Wednesday, September 22, 2021

ERROR 180-322: Statement is not valid or it is used out of proper order.

33         	Guessingrows=max;
34         run;

ERROR: Physical file does not exist, /data.csv. 
ERROR: Import unsuccessful.  See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
      

[Tom]

Where is the file you are trying to read?  

Is it available on a disk that is directly accessible from the server where SAS is running?

It kind of looks like you are trying to use SAS PCFILES Server to access files that are on a Windows machine (or at least accessible via the Windows machine) but not accessible directly from the machine where SAS itself is running.

But you have messed up the details. In particular you cannot use DBMS=CSV in PROC IMPORT.  That is for accessing files directly.  So if the file is directly accessible then you don't need all of those PCFILES server options like SERVER or SERVERUSER etc.

Also your filename is confusing as it is starting with the root node of / like you are trying to access a file in the Unix filesystem.  But then it includes a drive letter, C:, like you are trying to access a file on a Windows machine.

[Toni2]

Thank you for the support. The file that i try to import is in a shared drive. I don't know if it is directly accessible from SAS but when i run the below code it imports the Excel file successfully but with the issue that i refer at the beginning of this article (some character-number values appear as . dots in SAS). Does it mean that it is not directly accessible? 

I edit the most of the details in the version, server, port  and file name as well due to the sensitive of data for this reason doesn't make sense. However, i have used the below code and i am sure the path is correct

proc import out = data_raw dbms = excelcs datafile = "\\SAS Programs\data.xlsx" replace;
    sheet		= "data";
	version		= "2002";
	server		= "servername";
	port		= 5000;
	ServerUser = "&lwin";
    ServerPass = "&pwin"; 
run;

[Tom]

By using a DBMS type that requires the PC FILES server you are able to access a file using the machine where the PC FILES server is running.

The EXCELCS uses the PC FILES server.

I do not see anything in the documentation that says you can use it to access a plain old text file.

Note: All DBMS= specifications refer to local access, except for these specifications:

• DBMS=ACCESSCS
• DBMS=EXCELCS
• DBMS=PCFS

These files are accessed remotely by connecting to the SAS PC Files Server on Microsoft Windows.

[Toni2]

thank you