topic Re: Infile from other table in SAS Programming

Infile from other table

Aleixo — Thu, 05 Apr 2018 15:34:39 GMT

Hi,

Firstly i did some codes to import multiple files with getnames=yes, correctly done. But now delimiter not worked very well. And i had to do a different code like next one.

I am trying to read multiple files in a datastep with FORMAT, INFORMART, INFILE, INPUT, etc etc.

In statement infile i don't know how to do.

data WORK.INFORMACAO;
  infile datalines dsd truncover;
  input File:$300.;
datalines4;
C:\Users\A.csv
C:\Users\B.csv
;;;;

I did from a proc import but the delimiter is complicated because is not standard. I have to fix width each variable.

Now my datastep to "import" all files in my table:

Data importation;

lenght

.......

;

format

........

;

informat

.......

;

infile File filevar=File end=eof

delimiter=' '

MISSOVER

Firstobs=2;

input

.............

;

run;

My problem is in infile step. How i can bring WORK.INFORMACAO from other datastep to this datastep importation?

I did with a set statement but the result had only 2 observations because of data informacao had 2 observations.

Sorry for ask that question but i never found examples like this or explanation for this.

Regards,

Aleixo

Re: Infile from other table

RW9 — Thu, 05 Apr 2018 15:38:36 GMT

Use the call execute function. You can find another topic which shows it here:

https://communities.sas.com/t5/Base-SAS-Programming/Looping-through-Macro-Execution/m-p/451569/highlight/false#M113879

Re: Infile from other table

Tom — Thu, 05 Apr 2018 15:58:00 GMT

What the purpose of the list of file names? Do you want to read all of those files into a single SAS dataset? or process each one independently into its own SAS dataset?

Re: Infile from other table

Aleixo — Thu, 05 Apr 2018 16:02:42 GMT

Is better independently.

I try to put in a do while with no result, and try another way.

Good idea is to put in a datastep with the last name of the folder, like A or B. I have been difficulties to understand loop in SAS. Ina do while loop is easy but in a datastep not.

Re: Infile from other table

Aleixo — Thu, 05 Apr 2018 16:06:58 GMT

Yes but call execute is in a datastep with set on FilesInFolder.

Where you call execute? Datastep in a datastep? I think is a wrong idea.

Re: Infile from other table

Tom — Thu, 05 Apr 2018 16:13:35 GMT

@Aleixo wrote:

Is better independently.

I try to put in a do while with no result, and try another way.

Good idea is to put in a datastep with the last name of the folder, like A or B. I have been difficulties to understand loop in SAS. Ina do while loop is easy but in a datastep not.

I suspect that you are thinking that you could nest a data step within another data step. You can't do that in SAS. Each step runs on its own. You need to use some type of code generation. CALL EXECUTE() is one way to generate code. Creating a macro that takes the filename as one of its parameters will make it easier to generate the code from a list of files.

Re: Infile from other table

Reeza — Thu, 05 Apr 2018 16:17:58 GMT

Use a SET to include your data set and then you can pass the filenames in using FILEVAR. I believe the documentation under the FILEVAR option under INFILE statement has an example that's along this exact lines, passing the file names.

If the files are the same and in the same folder you can try a wild card approach instead, or you can wrap the program in a macro to call it once for each file.

It's not clear to me, which route you're trying to take.

Re: Infile from other table

Aleixo — Thu, 05 Apr 2018 16:20:29 GMT

I did that with filevar and statement SET to bring filenames to datasetp but the result was good for only 2 lines(2 observations) because that Set Work.INFORMATION had only 2 lines that was 2 files path. Do you see?

Re: Infile from other table

Reeza — Thu, 05 Apr 2018 16:25:48 GMT

Your question seems like a modification of this post, where instead of wildcard you need to pass the filename in, is that correct?

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

Answer:

The issue you're currently having is that it only processes two files, not the full list?

Answer:

Or is that it's not reading the files at all?

Answer:

When generalizing a process, you should first make it work for a single case. Can you provide working code for your one record and we can help you modify it to work with a data set that has the list of files that you want to read in. I don't recall how this method handles the header rows in each file, which is one thing you want to check.

Re: Infile from other table

SuryaKiran — Thu, 05 Apr 2018 16:25:51 GMT

You can use macros as follows:

   proc sql;
   select count(*), /* Total files */
		  file,/* File name */
		  scan(scan(file,count(file,'\')+1,'\','m'),1,'.') /* Using file name, name for output data set */
		into:Count,:files separated by ',', :DS_name separated by ','
   from WORK.INFORMACAO;
   quit;
%MACRO Read_Files();
%do i=1 %to &Count;
%let Current_File=%SCAN("&Files",&i,',');
%let Dataset_Name=%SCAN("&DS_Name",&i,',');
Data Read_&Dataset_Name;
Infile "&Current_File" firstobs=2;
input File $1000.;
run;
%end;
%Mend Read_Files;
%read_files;

Note: I didn't test the code, this is just an approach and you need to make some changes as needed.

Re: Infile from other table

Tom — Thu, 05 Apr 2018 16:26:34 GMT

Please explain the actual problem in more detail.

Do the input text files have a consistent format or not?

If not then you probably need to use PROC IMPORT so that it can try to figure out the format for you.

Can you use PROC IMPORT to read them? You mention that the delimiters are non-standard, but PROC IMPORT allows you to specify the delimiter. Do you mean that the delimiters vary between the input files? If so then how do you know which delimiter to use for a particular file in your list?

If they have a consistent format do you want to read all of the files into a single dataset? If so you can just use one data step.

data want ;
  set INFORMACAO ;
  filename=file;
  infile dummy filevar=filename dsd dlm='{' truncover firstobs=2 end=eof;
  do while (not eof);
    length ..... ;
    format ... ;
    informat ... ;
    input first_var_name -- last_var_name ;
    output;
  end;
run;

Re: Infile from other table

Aleixo — Thu, 05 Apr 2018 16:41:07 GMT

Thanks your code, i like your code. I try it with some errors that i have to study.

Re: Infile from other table

Aleixo — Thu, 05 Apr 2018 16:44:34 GMT

Yes this is a solution. I try and put all my import in same data. It is not my goal but was a very good solution. Now i had to separate the data to have data independentaly.

Thank you all very much

Regards,

Aleixo

Re: Infile from other table

Tom — Thu, 05 Apr 2018 17:06:50 GMT

Just use a BY statement or WHERE statement to process data from a single file.