BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aleixo
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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/highl...

Aleixo
Quartz | Level 8

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.

 

 

Tom
Super User Tom
Super User

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?

Aleixo
Quartz | Level 8

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.

 

 

Tom
Super User Tom
Super User

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

 

Reeza
Super User

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. 

 

Aleixo
Quartz | Level 8

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?

 

 

Reeza
Super User

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

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.

SuryaKiran
Meteorite | Level 14

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. 

Thanks,
Suryakiran
Aleixo
Quartz | Level 8

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

Tom
Super User Tom
Super User

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;
Aleixo
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1658 views
  • 1 like
  • 5 in conversation