Macro to import text files

[mhoward2]

Hi all, this really should be super basic, just trying to read in files and save them to work tables. Here's my code:

%Macro LoopFiles;
%DO I = 1 %TO 12;

DATA WORK.TEST_FILE_&I;
    LENGTH
        F1               $ 16 ;
    FORMAT
        F1               $CHAR16. ;
    INFORMAT
        F1               $CHAR16. ;
    INFILE "//w3r2/data/More Data/Tons of Data/Text Files/Test&I..txt"
        LRECL=16
        ENCODING="LATIN1"
        TERMSTR=CRLF
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        F1               : $CHAR16. ;
RUN;

%END;
%MEND LoopFiles;
%LoopFiles

I keep getting this error

    ERROR: Physical file does not exist, //w2rshr02/data/More Data/Tons of Data/Text Files/Test1.txt.

The file for sure exists and that is the correct path, any thoughts as to what I have to do for it to read in an external file path? Thanks in advance!

[Tom]

That is a strange path. It looks kind of like a Unix path but since it starts with the root node, but it has two slashes instead of one.

Note that if it is a Unix path then make sure that the case of all of the letters is correct. Unix filesystems are case sensitive.

If your SAS session is running on Windows instead and you meant to give it a Windows UNC path (\\servername\sharename\path\....) then change the slashes to backslashes. 

Are you sure that the file exists on the machine where SAS is running?  Is it accessible by the userid that is running the SAS process?

[mhoward2]

Thanks for the reply, I am trying to use the windows file path, I had changed the direction of the slashes because when I had the backslashes it gave me this error:

ERROR: Physical file does not exist,
/apps/sas/sasGridHome/config/compute/Lev1/SASApp/\\w2rshr02\data\More Data\Tons of Data\Text Files\Test1.txt.

where it adds that beginning part which I have no idea why it does that? Any and all help would be appreciated! Thanks!

[Tom]

---

@mhoward2 wrote:
Thanks for the reply, I am trying to use the windows file path, I had changed the direction of the slashes because when I had the backslashes it gave me this error:

ERROR: Physical file does not exist,
/apps/sas/sasGridHome/config/compute/Lev1/SASApp/\\w2rshr02\data\More Data\Tons of Data\Text Files\Test1.txt.

where it adds that beginning part which I have no idea why it does that? Any and all help would be appreciated! Thanks!

---

So that means your SAS session is running on Unix and you are asking it to find a file on some Windows share.  Ask your support team to mount that Windows Share on the Unix server and tell you want where they mounted it.

Otherwise first copy the file to some disk that is already on the Unix machine.

[mhoward2]

Ah interesting, I have no idea how Unix and windows interact. So the files would have to be a share drive that within Unix? Also then how does the import wizard pull in this file no problem?

Thanks again!

[Tom]

---

@mhoward2 wrote:
Ah interesting, I have no idea how Unix and windows interact. So the files would have to be a share drive that within Unix? Also then how does the import wizard pull in this file no problem?

Thanks again!

---

The details of how the Import Wizard works depends on what wizard you are talking about.  It sounds like you are using some process on your Windows PC to submit your SAS code.  Either the Windows application Enterprise Guide from SAS or a browser that is connected to a SAS/Share SAS/Studio server.  So in those two tools the import wizards will let you use the Windows program you can actually interacting directly with to select a file and then it will upload the file (or perhaps a modified version of the file) and submit SAS code to read from the file that it uploaded.

[mhoward2]

Yes I am using Enterprise Guide. So you're saying the import wizard has special magic power that allow it to read in windows files directly, but I can't bring them in by coding?

[Tom]

---

@mhoward2 wrote:
Yes I am using Enterprise Guide. So you're saying the import wizard has special magic power that allow it to read in windows files directly, but I can't bring them in by coding?

---

No. Enterprise Guide is a totally separate program from SAS itself.  It is a Windows application so it can read and write to the files that your PC can access.  When you want it to run SAS code it connects to a SAS server and submits the SAS code to there to run.

If you want your SAS session to access the files on your PC then connect to a SAS server running on your PC.  You will need to have SAS for Windows installed on your PC in addition to Enterprise Guide.   Or perhaps your company has a SAS server running on Windows instead of Unix that can access the shared folder.  

[mhoward2]

Haha I swear im not as dumb as im making myself sound, im probably dumber. I know we have a Unix server that I have to connect to when using Enterprise Guide and all but I have no idea what Unix actually is or why I need it.

But, so I can quit bothering you, can you give me a like a sentence or two that I can email to my SAS Admin team in order for them to understand why what im doing wont work and what needs to happen to fix it?

[Kurt_Bremser]

---

@mhoward2 wrote:
Haha I swear im not as dumb as im making myself sound, im probably dumber. I know we have a Unix server that I have to connect to when using Enterprise Guide and all but I have no idea what Unix actually is or why I need it.

But, so I can quit bothering you, can you give me a like a sentence or two that I can email to my SAS Admin team in order for them to understand why what im doing wont work and what needs to happen to fix it?

---

Ask your SAS server admin(s) to create a mount for

//w2rshr02/data/

on your SAS server, and tell you its location within the UNIX filesystem.

For clarification: in your first attempt, you used forward slashes; to UNIX, a path starting with a forward slash starts at the root of the filesystem, and is called absolute (two consecutive slashes are interpreted as one). When you changed to backward slashes, the path became relative, and the current working directory (CWD) of the workspace server process was prepended, resulting in the awkward file path in the ERROR message.

[Reeza]

If you're using EG, use the Copy Files task to move the files to the server and then import them. 

It's built in to EG now, though if you were running an older version it needs to be installed. 

The GUI works because it can load the file one a time manually and import it, but the capability to do that at scale is locked down as a design/security feature. 

[mhoward2]

Hey, thanks for this! I was unaware of it, I just tried it with a single txt file and it worked! How would I do this with a whole folder of text files? Is that even possible with this task?

[Reeza]

There is bulk processing, think you use the * as a wildcard. See details in this blog post.
https://blogs.sas.com/content/sasdummy/2020/05/19/copy-files-in-sas-eg/

[Reeza]

Note the limitation though - it's a significant one unfortunately.
This task does not generate a SAS program that you can reuse in a batch SAS process or SAS stored process.

[mhoward2]

Thank you very much! I really appreciate the help!