BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

View solution in original post

15 REPLIES 15
yabwon
Onyx | Level 15

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

 

 

 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ronein
Meteorite | Level 14
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?
Quentin
Super User

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.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Ronein
Meteorite | Level 14
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..
yabwon
Onyx | Level 15
"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
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ronein
Meteorite | Level 14
How can I know if the location of file is windows share? sharepoint? one drive? in the cloud
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Tom
Super User Tom
Super User

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.

Ronein
Meteorite | Level 14
My sas is running on Unix. How can it help me solve my problem?
Ksharp
Super User
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.
Ronein
Meteorite | Level 14
Can you please explain what is ABSOLUTE PATH ? Is it different than the path that I saw when I opened the excel file?
Patrick
Opal | Level 21

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

Ksharp
Super User
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.
Kurt_Bremser
Super User

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 15 replies
  • 1311 views
  • 10 likes
  • 7 in conversation