topic Re: pc files server in SAS Procedures

pc files server

Tal — Wed, 12 Apr 2017 18:04:49 GMT

hi,

so i just got pc files server installed and i read many articles saying one should be able to access pc files from unix sas .

I have SAS 64bit and Ofifce 32 bit

i tried my code on pc sas first and it worked:

PROC IMPORT DATATABLE=SLOCCov DBMS=ACCESSCS OUT=TEMP1 REPLACE;;

DATABASE="Y:\xx\yyy\zzz.accdb";

DBDSOPTS='READBUFF=30';

RUN;

but then when i ran it on EG SAS(Remote) i got an error

PROC IMPORT DATATABLE=SLOCCov DBMS=ACCESSCS OUT=TEMP1 REPLACE;;

DATABASE="Y:\xx\yyy\zzz.accdb";

PORT=9261;

SERVER="xyz.abc.com";

DBDSOPTS='READBUFF=30';

RUN;

ERROR: A socket routine returned error: The connection was refused.

ERROR: Failed to connect to the Server: *******.com.

1. Can i really import pc files into UNIX SAS?

2.Name of server will be my computer name right?

3. Any insights on this error please

thx

Re: pc files server

jklaverstijn — Wed, 12 Apr 2017 19:11:40 GMT

Hi @Tal,

1) yes you can.

2) The name of the server should be the name of the server running the pc files server. On that server it will listen to the configured port, in your case 9261.

3) I would start with validating the network connectivity:

* Are server name and port correct?

* Is there basic network connectivity between the two (they may be on different networks). Use the ping command on the Linux machine to test that.

ping xyz.abc.com

* Use telnet (if availble) on the Linux host to determine if you can reach the port on the pc files server

ftp xyz.abc.com 9261

* If these tests do not indicate a working connection, there may be a firewall on either machines or en route that may block your traffic. Discuss with your network staff.

Only if this checks out (which I think it will not do), you can look into the SAS side of things.

Let us know how you fare.

Kind regards,

- Jan.

Re: pc files server

Haikuo — Wed, 12 Apr 2017 19:27:58 GMT

DATABASE="Y:\xx\yyy\zzz.accdb";

Y (if pointing to a network share) seems to be a drive letter validated only by your PC. Try to use universal network address instead, such as \\share\blah\xx\yyy\zzz.accdb

Re: pc files server

Tal — Wed, 12 Apr 2017 19:44:36 GMT

Thanks Jan

i did the ping test and it never stopped pinging 🙂

i did the telnet test and connection to 6291 was refused but i could reach 6921 and i someone mentioned i should use this port so still a possible firewall issue ?

Re: pc files server

Tal — Wed, 12 Apr 2017 19:46:16 GMT

hey Haikuo yes i changed the map to reflect the absolute path not the path UNIX cannt recognize

thank you

Re: pc files server

jklaverstijn — Wed, 12 Apr 2017 19:54:08 GMT

If I'm not mistaken the Linux should not need access to the path. It is validated and accessed on the Windows server. On that server it should be a valid path, either by drive letter or UNC.

You mentioned port to 9261 in your initial post. Make sure you use the one that the pc files server is configured to listen to. If telnet gives a "connection refused" your best bet would be the firewall.

Re: pc files server

Tal — Wed, 12 Apr 2017 20:00:41 GMT

so i changed the path and also added the credentials i use to log into windows and changed the port to 9621 as advised by some of the guys in different depts who has used it

PROC IMPORT OUT= TEMP DATAFILE= "\\aaa\vv\bbb\tt\ccc\dd\Book1.xlsx" DBMS=EXCELCS REPLACE;

PORT=9621;

SERVER="xyz.abc.com";

SHEET='Sheet1';

SERVERUSER='domain\userid';

SERVERPASS='****';

RUN;

and the error :

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER='<domain\username>';

SERVERPASS='<password>'; enclosed in quotes).

so looks like i dont have the right SERVER's name above huh?

ps.this is an excel file import as oopose the access i initially had posted

Re: pc files server

Haikuo — Wed, 12 Apr 2017 20:07:29 GMT

Make sure 'domain\userid' has the write access to "\\aaa\vv\bbb\tt\ccc\dd". You may need check with your network folks who manage the active domain.

Re: pc files server

jklaverstijn — Wed, 12 Apr 2017 20:30:07 GMT

Maybe it indicates you Do have the correct server. Otherwise I would expect different more basic errors. Just maybe this messages tells us like it is?

And what happens if you try the pcfiles libname engine?:

LIBNAME myxlsx PCFILES SERVER="xyz.abc.com" PATH='\\aaa\vv\bbb\tt\ccc\dd\Book1.xlsx' SERVERUSER='domain\userid' SERVERPASS='****';

Re: pc files server

Tal — Thu, 13 Apr 2017 13:38:05 GMT

hello

@Haikuo i do have read/write access to that drive

"Special Permisisons " is checked under "Permissions" but i can save,delete,read in this folder

@Jan

9621 is the right port i checked that and it connects

ftp "Txyz.abc.com" 9621

Connected to xyz.abc.com.

i did the ping test again and it never stops

64 bytes from ****: icmp_seq=4 ttl=121 time=7 ms
64 bytes from ****: icmp_seq=5 ttl=121 time=6 ms
64 bytes from ****: icmp_seq=6 ttl=121 time=6 ms

............................

..............................

i ran these again:

PROC IMPORT OUT= TEMP DATAFILE= "\\aaa\vv\bbb\tt\ccc\dd\Book1.xlsx" DBMS=EXCELCS REPLACE;
PORT=9621;
SERVER="xyz.abc.com";
SHEET='Sheet1';
SERVERUSER='domain\userid';
SERVERPASS='******';
RUN;

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER='<domain\username>'; SERVERPASS='<password>'; enclosed in quotes).

libname mylib pcfiles
path="\\aaa\vv\bbb\tt\ccc\dd\Book1.xlsx"
server="xyz.abc.com";
port=9621
SERVERUSER='domain\userid'
SERVERPASS='*****';

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER=<username> SERVERPASS=<password> on libname statement).

I am clueless 😞

Re: pc files server

Haikuo — Thu, 13 Apr 2017 15:43:55 GMT

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER='<domain\username>'; SERVERPASS='<password>'; enclosed in quotes).

This shows your credential is not accepted by the server "xyz.abc.com, the one runs SAS pcfiles server. Do you have a SAS admin managing your permission?

Re: pc files server

Tal — Thu, 13 Apr 2017 16:50:37 GMT

trying to find out if the server's name i have specified in the code here matches the server's name the pc files server is installed on.

i am using my computer's name a sa server name

managing my permission as in not able to access remote servers or something like it?

Re: pc files server

Haikuo — Thu, 13 Apr 2017 17:00:43 GMT

Are you saying you are using your PC as remote server running SAS pcfiles server? In that case, you need to make sure you have permisson to remote into your own PC. Yes, it sounds wierd, but local permission to your PC does NOT grant your remote permission to the same box automatically. You will need network security folks to fix that. At least, you will need to have admin access to your own pc to make some changes.

Re: pc files server

Tal — Thu, 13 Apr 2017 17:04:44 GMT

yes, i am running EG SAS Remote to access files from my network drives and import them into UNIX.

i thought

SERVERUSER='domain\userid'

SERVERPASS='****';

is how i access my laptop from the unix sas sever,no?

The credential above is my windows login

Re: pc files server

Tom — Thu, 13 Apr 2017 17:13:33 GMT

So you are running PC Files server on your laptop?

Does EG connect to SAS running on your PC also? Or to some other machine? Is that machine running Windows or Unix?

Re: pc files server

Tal — Thu, 13 Apr 2017 17:38:43 GMT

pc files server running on my laptop(windows machine)

EG SAS runs on my laptop too but SAS Remote (UNIX)

Re: pc files server

Tal — Wed, 03 May 2017 16:01:34 GMT

so this actually works if trying to access files from EG SAS located in my c drive:

proc import out= temp datafile= "C:\Users\Redirection\userid\Desktop\book1.xlsx" dbms=excelcs replace;

port=9621;

server="XYZ12345678";

sheet='Sheet1';

run;

So i guess no way files in the share drives can be accessed ?

Re: pc files server

Tom — Wed, 03 May 2017 16:10:32 GMT

In theory it should work. As long as the PC files server process can access the share drive. Which if it is running on your PC then I assume that means if you can access the shared drive.

You should be able change the path to use the UNC path to the shared drive. So if there is a file name 'somebook1.xlsx' in a folder named 'somefolder' on the shared drive that is shared as '\\servername\sharename' then the code would look like this.

proc import out= temp replace
  dbms=excelcs 
  datafile= "\\servername\sharename\somefolder\somebook1.xlsx"
;
  port=9621;
  server="XYZ12345678";
  sheet='Sheet1';
run;

Re: pc files server

Tal — Wed, 03 May 2017 17:03:35 GMT

thanks Tom

i did try it just now :

proc import out= temp datafile= " \\xxx\corp\yyy\TOR1\zzz\mm\Book1.xlsx" dbms=excelcs replace;

port=9621;

server="***";

sheet='Sheet1';

run;

ERROR: Unable to open file \\xxx\corp\yyy\TOR1\zzz\mm\Book1.xlsx. It does not exist or it

is already opened exclusively by another user, or you need permission to view its data.

i created Book1 and i can access it ....

Re: pc files server

Go — Tue, 09 May 2017 17:35:08 GMT

Hi Tal and Tom,

I have exactly same issue, could you resolve this ??

PROC EXPORT

DBMS=ACCESSCS

DATA=SASHELP.CARS

TABLE="CARS"

REPLACE;

DATABASE='\\WILPMVDFS01.juniper.com\Shared\SAS_Admin\Govardan\SASHELPCARS.ACCDB';

SERVER_NAME='WILPLM26266.juniper.com';

SERVERUSER='dryrock\gnayaki';

SERVERPASS='***;

PORT=9621;RUN;

DOESN’T WORK GIVES BELOW ERROR------

ERROR: Server is unable to authenticate user credentials. (missing or invalid SERVERUSER='<domain\username>';

SERVERPASS='<password>'; enclosed in quotes)

PROC EXPORT

dbms=accesscs

data=sashelp.cars

table="cars"

replace;

database='C:\Users\gnayaki\Desktop\DESKTOP_june13\newcars1.accdb';

server='WILPLM26266.juniper.com';

port=9621;run;

WORKS FINE