topic Re: How to import the external data (multiple delimeter file) into SAS in SAS Programming

How to import the external data (multiple delimeter file) into SAS

vThanu — Thu, 11 Apr 2019 14:48:49 GMT

If I have a multiple files with difference extension and I want to read all the files into SAS at once... how I have to do?

for example in a specified location I have multiple file like 2 excel (.xls) 1 CSV(.csv) , 1 text (.txt) & 1 Access (.mdb) files.

I have to read all these file into sas with single step...

Thanks in advance

Re: How to import the external data (multiple delimeter file) into SAS

Reeza — Thu, 11 Apr 2019 15:12:42 GMT

@vThanu wrote:

If I have a multiple files with difference extension and I want to read all the files into SAS at once... how I have to do?

for example in a specified location I have multiple file like 2 excel (.xls) 1 CSV(.csv) , 1 text (.txt) & 1 Access (.mdb) files.

I have to read all these file into sas with single step...

Thanks in advance

You can't import them all in one step. I suppose you could write a data step to generate the code to import all if you wanted to count that as one step, using call execute but that seems pointless.

data _null_;

call execute("proc import out=want1 datafile='/folders/myfolders/demo.csv' dmbs=csv replace; run;");

call execute("proc import out=want2 datafile='/folders/myfolders/demo2.csv' dmbs=csv replace; run;");

....

....

run;

Re: How to import the external data (multiple delimeter file) into SAS

Shmuel — Thu, 11 Apr 2019 15:18:25 GMT

For each file you need a specific filename:

filename file1 excel "<path and file-1 name.xls>";
filename file2 excel "<path and file-2 name.xls>";
filename file3 "<path and file-3 name.txt>" dlm="<delimiters>";
filename file4 "<path and file-4 name.csv>" dlm=",";
/* and so on */

data want;
   infile file1 truncover end=eof1;;
   input <variables with their informats>;
   
   if eof1 then do;
      infile file2 truncover end=eof2;
      input <variables with their informats>;
   end;

   if eof2 then do;
      infile file3 truncover end=eof3;
      input <variables with their informats>;
   end;

  ...... etc. ......

run;

Re: How to import the external data (multiple delimeter file) into SAS

ballardw — Thu, 11 Apr 2019 15:21:19 GMT

@vThanu wrote:

I have to read all these file into sas with single step...

Why?

Re: How to import the external data (multiple delimeter file) into SAS

vThanu — Thu, 11 Apr 2019 15:32:32 GMT

to read the data for preparing intermediate data set and for analysis

Re: How to import the external data (multiple delimeter file) into SAS

vThanu — Thu, 11 Apr 2019 15:36:00 GMT

Using Call execute here we are invoking each file every time.... if we have more files those many times invoking is time taken process and for each extensionn we are invoking.... any other way

Re: How to import the external data (multiple delimeter file) into SAS

ballardw — Thu, 11 Apr 2019 15:48:53 GMT

@vThanu wrote:
to read the data for preparing intermediate data set and for analysis

That does not require "reading in on step". You can read each file independently and then combine the data afterwards. Though you do want to make sure that the variable types and lengths are the same for commonly named variables across the sets.

I would tend to guess that different file formats means the content varies a little between the files and it is likely easier to adjust several hopefully simple data steps reading each file than to try to write one very likely to be extremely complicated data step.

Re: How to import the external data (multiple delimeter file) into SAS

Reeza — Thu, 11 Apr 2019 19:25:11 GMT

@vThanu wrote:
Using Call execute here we are invoking each file every time.... if we have more files those many times invoking is time taken process and for each extensionn we are invoking.... any other way

None that I'm aware of from any tool that I work with, which includes Python, R, or SAS.

If the files are structured exactly the same each time and there's a defined ETL process, you could streamline it for each type of files but not different types because they have to be handled differently. Reading all CSVs into a single file is easy - I have an example of that in the Library section of this website.

Excel is a pain because it doesn't enforce types so you will have to verify these files for each one to ensure you have the correct types needed.

MS Access is clean and easier because it enforces types, but each DB would need to be done separately as well.

Is this a one time process? Are you using SAS DI?

How different are the files? Why can't you import one at a time.

You can streamline the process for sure, having a process to scan the folder, find all the different file types and import each file type and save it within a single program. But not a single procedure.