Import from shared drive

[ywon111]

Trying to import an excel workbook from a shared drive (local file server) into SAS. 

This is what I got so far with error - ERROR: Import unsuccessful. See SAS Log for details.

proc import out = test
datafile = "\\xxxx.xxx.xxxx.xx\shared\test.xlsx"
DBMS=CSV REPLACE;
GETNAMES=NO;
DATAROW=2;
run;

Appreciate the help.

[Reeza]

Where is SAS installed, locally or on a server?

The path has to be relative to the SAS installation.

---

@ywon111 wrote:

Trying to import an excel workbook from a shared drive (local file server) into SAS. 

This is what I got so far with error - ERROR: Import unsuccessful. See SAS Log for details.

proc import out = test
datafile = "\\xxxx.xxx.xxxx.xx\shared\test.xlsx"
DBMS=CSV REPLACE;
GETNAMES=NO;
DATAROW=2;
run;

Appreciate the help.

---

[ywon111]

Thanks for the quick response.
Think it is installed on a server. How can I change the codes to import the excel file?

[Reeza]

You'll have to make sure the server has access to the shared drive and know how to access it from there. Unfortunately that usually requires talking to your IT area to understand how it's set up. A drive you see on your desktop could be the M drive for example but it could be mapped to G on the server. 

The other method, is if you can find the file in Servers Files and Folders Pane (SAS Studio) you can then right click it and find the path and use that. 

If you're using SAS Base or EG there are similar methods to find the path. 

[Kurt_Bremser]

Please run this:

%put &sysscp;

and post the log from it. This will reveal the operating system of your SAS server.

[ywon111]

Part of the log shows this, does it mean it is C drive?

STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")

[Kurt_Bremser]

No, that's only a message from the Enterprise Guide to itself where to fetch the stylesheet for displaying the output. It is part of the ODS tagsets.sasreport13 statement.

I am interested in the result of the %put, on University Edition it looks like this:

 73         %put &sysscp;
 LIN X64
 

[ywon111]

This is what I get

11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")

[Kurt_Bremser]

This is only part of the log. Te whole log, when I run the %put via Enterprise Guide, is this:

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Programm (2)';
4          %LET _CLIENTPROCESSFLOWNAME='Prozessfluss';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         ODS LISTING GPATH=&sasworklocation;
15         FILENAME EGSR TEMP;
16         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
17             STYLE=EGDefault
18             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/EGDefault.css")
19             NOGTITLE
20             NOGFOOTNOTE
21             GPATH=&sasworklocation
22             ENCODING=UTF8
23             options(rolap="on")
24         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
25         
26         GOPTIONS ACCESSIBLE;
27         %put &sysscp;
AIX 64
28         
29         GOPTIONS NOACCESSIBLE;
30         %LET _CLIENTTASKLABEL=;
31         %LET _CLIENTPROCESSFLOWNAME=;
32         %LET _CLIENTPROJECTPATH=;
33         %LET _CLIENTPROJECTPATHHOST=;
34         %LET _CLIENTPROJECTNAME=;
35         %LET _SASPROGRAMFILE=;
36         %LET _SASPROGRAMFILEHOST=;
37         
38         ;*';*";*/;quit;run;
39         ODS _ALL_ CLOSE;
40         
41         
42         QUIT; RUN;
43         

[ywon111]

1 The SAS System 22:37 Thursday, April 16, 2020

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=PNG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 %put &sysscp;
LIN X64
27
28 GOPTIONS NOACCESSIBLE;
29 %LET _CLIENTTASKLABEL=;
30 %LET _CLIENTPROCESSFLOWNAME=;
31 %LET _CLIENTPROJECTPATH=;
32 %LET _CLIENTPROJECTPATHHOST=;
33 %LET _CLIENTPROJECTNAME=;
34 %LET _SASPROGRAMFILE=;
35 %LET _SASPROGRAMFILEHOST=;
36
37 ;*';*";*/;quit;run;
38 ODS _ALL_ CLOSE;
39
40
41 QUIT; RUN;
42

[Kurt_Bremser]

So your SAS runs on a Linux server. Ask your SAS administrator if your network share is mounted on that server, and if yes, what the Linux path to it is.

Something like

/mnt/location

or

/shared/location

Note that all these start with a forward slash; this means that they are absolute path names starting at the system root. See https://en.wikipedia.org/wiki/Unix_filesystem for more information.

[ballardw · Accepted solution]

---

@ywon111 wrote:

Trying to import an excel workbook from a shared drive (local file server) into SAS. 

This is what I got so far with error - ERROR: Import unsuccessful. See SAS Log for details.

proc import out = test
datafile = "\\xxxx.xxx.xxxx.xx\shared\test.xlsx"
DBMS=CSV REPLACE;
GETNAMES=NO;
DATAROW=2;
run;

Appreciate the help.

---

Your import code says the file is CSV but the file name is xlsx. It is entirely possible that you source file is not actually CSV. In which case you are telling Import to attempt to read a simple text file structure that is not the same as in XLSX.

Can you open that source file in a simple text editor (NOT Excel) such as Notepad? If you do an do not see values separated by commas then the file is in a different format and you need to use the correct DBMS value.