topic Re: Find path of excel file to import by code in SAS Programming

Find path of excel file to import by code

Ronein — Tue, 26 Nov 2024 10:13:42 GMT

Hello

I want to import EXCEL file that is located in specific location (These are external files that other team create).

When I am using Import Wizard then all is working well and I succeed import it and I see the code that was created :

data mydata;
INFILE '/usr/local/saswork/SAS_workD18B00007116_LINX107717A14/#LN00389'
        LRECL=91
        ENCODING="HEBREW"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        CUST_ID         : $CHAR19.
        Branch     : ?? BEST3.
        Numerator     : $CHAR12.
        Date1   : ?? YYMMDD10.
        Time1     : ?? TIME8.
        Date2     : ?? YYMMDD10.
        Amnt    : ?? COMMA18.
        ProductID     : ?? BEST4. ;
run;

The issue is that in the future I want to write a code (NOT use Import wizard) that import the file.

I know that the path that was created (by using import wizard) is a temporary path

'/usr/local/saswork/SAS_workD18B00007116_LINX107717A14/#LN00389'

When I open Excel file I found the path of the file :

"//leumisrv1/Shidurim/AFT/Nihul_Sikunim/Credit_Risks/Actimize_Loans_Reports/Loan_Transaction_031124.CSV"

I changed the temporary path to the path that I saw but I see an error that SAS doesnt find this path

I also tried to change the direction on slash (from / to \) and still same error

What should I do please?

Re: Find path of excel file to import by code

yabwon — Tue, 26 Nov 2024 10:57:14 GMT

First of all you are not importing Excel file, but comma separated text file, CSV!

Second thing, because you didn't provide any log/error message/etc. I can only guess...

When you are doing it through SAS EG (the wizard) you: 1) pick up the file on your local machine {Windows?}, 2) EG moves it to server (makes a copy) and then 3) SAS imports the file on the server.

If you want to import a file that file has to be visible to the server you running SAS on!

So... copy the file on the server and you will be able to import it.

Bart

Re: Find path of excel file to import by code

Ronein — Tue, 26 Nov 2024 15:04:43 GMT

Thank you.
It is true that the file is not in sas server.
It means that I cannot see the file via sas .
I can only see the file from the computer local server.
It is not my decision where to locate these files and currently they are not loacted in sas server location. My question- Is it possible to import csv file that is not located in sas server? What action should I do in order to be able to import it via code?

Re: Find path of excel file to import by code

Quentin — Tue, 26 Nov 2024 15:36:30 GMT

If you want SAS to import a file, the file has to be stored in a location that is accessible to your SAS server. So it could be stored on the SAS server, or on another server which can can be reached from your SAS server.

When you say you used an Import Wizard to generate the original code, was that an important wizard from Enterprise Guide? If you run Enterprise Guide on a Windows PC and use a wizard to import a file to the SAS session, Enterprise Guide will sometimes upload the file from Windows to the SAS server for you, in the background. It looks like this might be what happened, because the code you shared points to a file in your linux work directory:

INFILE '/usr/local/saswork/SAS_workD18B00007116_LINX107717A14/#LN00389'

But that automatic upload is enterprise guide magic, and it works because enterprise guide is running on your PC, so can see all the files that you can see in Windows. It's not SAS code that is uploading the file.

Depending on where the file is stored (windows share? sharepoint? one drive? in the cloud?) there are different options for either automating the process of copying the file to your SAS server, or giving your SAS server access to see the file.

Re: Find path of excel file to import by code

yabwon — Tue, 26 Nov 2024 15:53:32 GMT

1) "What action should I do in order to be able to import it via code?"

Please forgive me self quoting:

If you want to import a file that file has to be visible to the server you running SAS on!

So... copy the file on the server and you will be able to import it.

2) "Is it possible to import csv file that is not located in sas server?"

Yes, if a) SAS server can see the file, or b) if the file is accessible by Filename URL statement of Proc Http so it can be downloaded to the server drive.

filename f URL "Https://some.web.address.com/location/file.csv" lrecl=1 recfm=n;
filename s "/location/on/my/server/file.csv" lrecl=1 recfm=n;

data _null_;
  rc = fcopy("f","s");
  rctxt=sysmsg();
  if rc then put rctxt;
run;

proc import ...

Bart

Re: Find path of excel file to import by code

Tom — Tue, 26 Nov 2024 16:19:23 GMT

Enterprise Guide is running on a Windows machine (either yours or some Windows server you connected to) that can access the Windows Share where the file lives. Perhaps that share is the one named \\leumisrv1\Shidurim that you mentioned in your post. Perhaps it is somewhere else, it is whatever location you found it with the Import Wizard.

If you want SAS code to find the file then the server SAS is running on needs access to that file.

You could ask the SAS administrators to mount the share on the server. If SAS is running on a Unix server then they will mount is at some location in the Unix filesystem tree. So perhaps something like

/windows_shares/leumisrv1/Shidurim/

You could ask for permission to copy the file from some server that has a access. Using something like the unix scp or sftp commands.

Re: Find path of excel file to import by code

Ronein — Tue, 26 Nov 2024 19:52:00 GMT

If I used import wizard and succeed import the file then I think it means that the file is visible by sas? My problem is that I didn't succeed create a code that import it..

Re: Find path of excel file to import by code

Ronein — Tue, 26 Nov 2024 19:53:23 GMT

My sas is running on Unix. How can it help me solve my problem?

Re: Find path of excel file to import by code

Ronein — Tue, 26 Nov 2024 19:54:54 GMT

How can I know if the location of file is windows share? sharepoint? one drive? in the cloud

Re: Find path of excel file to import by code

yabwon — Tue, 26 Nov 2024 20:39:01 GMT

"If I used import wizard and succeed import the file then I think it means that the file is visible by sas?"
No, it does NOT mean that the file is visible by SAS.
It only means that (as I wrote in my first response) EG can see the local file and makes a copy of it to the server, so that SAS can import the copy.
Bart

Re: Find path of excel file to import by code

Ksharp — Wed, 27 Nov 2024 01:57:16 GMT

Talk to your UNIX Administrator to tell you the real ABSOLUTE PATH of this excel file, and make sure you have READ right when you are using sas to import this excel file.

Re: Find path of excel file to import by code

Ronein — Wed, 27 Nov 2024 10:26:35 GMT

Can you please explain what is ABSOLUTE PATH ? Is it different than the path that I saw when I opened the excel file?

Re: Find path of excel file to import by code

Patrick — Wed, 27 Nov 2024 11:50:55 GMT

@Ronein

EG is installed on your local laptop (or eventually something like a Citrix server). From a SAS perspective EG is running on the client machine and connects to a SAS server where SAS code runs.

When you're using the EG import wizard then from what I understand the EG client on Windows reads the Excel on the client side and uploads the data as text file to SAS WORK. The generated SAS code is for reading this text file.

If you want to run SAS code that directly reads an Excel file then such a file needs to be stored under a path that's directly accessible to the SAS Server (unlike with the EG import wizard where you access the file from you local client environment).

Not all (or none) shares/mapped drives that are accessible from your laptop will also be accessible from you SAS server. Talk to your SAS admin to figure out if and where there is a location where "PC users" can drop a file that's also accessible to your SAS server.

Re: Find path of excel file to import by code

Ksharp — Thu, 28 Nov 2024 01:39:28 GMT

ABSOLUTE PATH is like:
/root/user/ronein/this.xlsx

RELATIVE PATH is like:
/roneni/this.xlsx

P.S.
Here /root/ is the ROOT directory in UNIX OS.

Re: Find path of excel file to import by code

Kurt_Bremser — Thu, 28 Nov 2024 11:19:51 GMT

@Ksharp wrote:
ABSOLUTE PATH is like:
/root/user/ronein/this.xlsx

RELATIVE PATH is like:
/roneni/this.xlsx

P.S.
Here /root/ is the ROOT directory in UNIX OS.

On UNIX, everything that starts with a slash is an absolute path. So your second example should be

roneni/this.xlsx

so the system would look for a directory called roneni in the current working directory of the calling process.

The "directory name" for the system root is a single slash.

Re: Find path of excel file to import by code

Kurt_Bremser — Thu, 28 Nov 2024 11:23:38 GMT

On UNIX, an absolute path starts at the system root directory, symbolized by a single slash. Whenever a path is encountered that does not start with a single slash, the system starts to look in the current working directory of the calling process; with a SAS workspace server, this is usually the directory in which the WorkspaceServer.sh script is located, somewhere in the SAS configuration tree.