topic Re: Import from shared drive in SAS Programming

Import from shared drive

ywon111 — Thu, 16 Apr 2020 04:01:43 GMT

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.

Re: Import from shared drive

Reeza — Thu, 16 Apr 2020 04:10:59 GMT

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.

Re: Import from shared drive

ywon111 — Thu, 16 Apr 2020 04:17:14 GMT

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

Re: Import from shared drive

Reeza — Thu, 16 Apr 2020 04:29:52 GMT

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.

Re: Import from shared drive

Kurt_Bremser — Thu, 16 Apr 2020 07:02:36 GMT

Please run this:

%put &sysscp;

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

Re: Import from shared drive

ywon111 — Thu, 16 Apr 2020 07:09:59 GMT

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")

Re: Import from shared drive

Kurt_Bremser — Thu, 16 Apr 2020 07:21:58 GMT

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

Re: Import from shared drive

ywon111 — Thu, 16 Apr 2020 10:47:29 GMT

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")

Re: Import from shared drive

Kurt_Bremser — Thu, 16 Apr 2020 11:07:17 GMT

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

Re: Import from shared drive

ywon111 — Thu, 16 Apr 2020 11:16:35 GMT

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

Re: Import from shared drive

Kurt_Bremser — Thu, 16 Apr 2020 11:24:51 GMT

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

/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.

Re: Import from shared drive

ballardw — Thu, 16 Apr 2020 14:49:02 GMT

@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.