topic Re: Macro to import text files in SAS Programming

Macro to import text files

mhoward2 — Wed, 10 Jun 2020 21:55:08 GMT

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!

Re: Macro to import text files

Tom — Wed, 10 Jun 2020 22:06:46 GMT

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?

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 22:08:59 GMT

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!

Re: Macro to import text files

Tom — Wed, 10 Jun 2020 22:11:29 GMT

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

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 22:18:07 GMT

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!

Re: Macro to import text files

ballardw — Wed, 10 Jun 2020 22:21:39 GMT

Please not that the error has a different path then that in your code.

It is best to copy the code from the log along with errors and paste all of that into a code box. That way we see the entire code as actually submitted.

Is the //w3r2 supposed to indicate a network server on your network? If not that might be the issue as it may be treated that way.

And if that is intended are you working with a SAS server set up? The server might be using different permissions than yours attempting to access the file.

And there may not be a need for a macro at all as you can read multiple text files with a single data step, if that would be preferred instead of reading into 12 data sets. Since I don't see anything on your INFILE like FIRSTOBS where you are skipping header rows this would be fairly easy with a long FILENAME statement.

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 22:25:00 GMT

The reason the share drive address dont match up is me messing up which copy and paste it should be, thanks for catching that. The correct address is the error.

How do I read multiple in a single step if I cant even read in one? haha I appreciate any and all advice!

Re: Macro to import text files

ballardw — Wed, 10 Jun 2020 22:39:28 GMT

When you get the path issue straightened out you might try your Infile to use \test*.txt IF all of the Test files in the folder have the same layout. The wildcard * would get all the files in the folder whose names start with "Test".

Or for specific files only, such as you want to read 1 to 12 of 100 files a filename similar to this:

Filename toread ("<path>\test1.txt" "<path>\test2.txt" "<path>\test31.txt") <other filename options>;

and use: Infile Toread <remaining Infile options>;

If you want to know which file each record came from then you can investigate the mysteries of saving the value of a Filename=<some variable name> option on the Infile statement. Not hard, routine and tedious, to have the file.

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 22:48:04 GMT

All the files are basically identical in format. Just different values. The filename thing isnt the issue since its the correct file name in the program. SAS still cannot read in the file though, exact same error.

Re: Macro to import text files

Tom — Wed, 10 Jun 2020 22:53:51 GMT

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

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 22:51:55 GMT

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?

Re: Macro to import text files

Tom — Wed, 10 Jun 2020 22:57:38 GMT

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

Re: Macro to import text files

Reeza — Wed, 10 Jun 2020 23:07:13 GMT

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.

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 23:10:27 GMT

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?

Re: Macro to import text files

mhoward2 — Wed, 10 Jun 2020 23:23:26 GMT

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?

Re: Macro to import text files

Kurt_Bremser — Thu, 11 Jun 2020 05:32:39 GMT

@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 import wizard in Enterprise Guide uses your local Windows to read the file, transfers the content via the IOM bridge to the SAS workspace server process's WORK, and sends code to import the file from there. If you want to read the file without the EG interaction (i.e. you want to run the code in batch), the file needs to be accessible from the UNIX filesystem of the server.

Re: Macro to import text files

Kurt_Bremser — Thu, 11 Jun 2020 06:30:52 GMT

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

Re: Macro to import text files

Reeza — Thu, 11 Jun 2020 15:19:02 GMT

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/

Re: Macro to import text files

Reeza — Thu, 11 Jun 2020 15:20:13 GMT

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.

Re: Macro to import text files

mhoward2 — Thu, 11 Jun 2020 15:37:10 GMT

Thank you very much! I really appreciate the help!