Desktop productivity for business analysts and programmers

Combine multiple imported files into one data set

Reply
New Contributor
Posts: 4

Combine multiple imported files into one data set

Hi All,

 

im very very new to SAS, so hoping someone can help. I have multiple csv files in a folder that i need to create a data set for them.

 

Below is a macro that works perfectly in identifying all the csv files in that folder then creating seperate data sets.

 

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;          

   %let filrf=mydir;    
   %let rc=%sysfunc(filename(filrf,&dir)); 
   %let did=%sysfunc(dopen(&filrf));
    %if &did ne 0 %then %do;   
   %let memcnt=%sysfunc(dnum(&did));    

    %do i=1 %to &memcnt;              
                       
      %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);                    
                    
      %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;
       %if %superq(ext) = %superq(name) %then %do;                         
          %let cnt=%eval(&cnt+1);       
          %put %qsysfunc(dread(&did,&i));  
          proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 
           dbms=csv replace;            
          run;          
       %end; 
      %end;  

    %end;
      %end;
  %else %put &dir cannot be open.;
  %let rc=%sysfunc(dclose(&did));      
             
 %mend drive;
 
%drive(H:\Desktop\Other Extracts,csv) 

How can i create one dataset in that macro above, so all in that one macro to give me one dataset from 6 or 7 or however many csv files are in that folder.

 

NOTE: all have the same number of columns and the columns are the same name, so i can use the top row of any of the files. Im using SAS 9.3

 

Also, new csv files will be added to that folder (could be monthly, weekly, daily, not sure). How do i integrate this with my data set? Do i simply run it again and again every time there's a new file? (this seems a bit silly)

 

Thanks in advance, appreciate any help or guidance on this.

 

 

Contributor
Posts: 34

Re: Combine multiple imported files into one data set

You can add a proc append after the proc import:

 

ex

proc append base = lib.yourbasetable new = dsn&cnt;

run;

 

 

 

If you move the files you have already read to a processed folder, you can run this macro every time you get new files. Can you run x commands in your environment? If so, you can use the cmd move command:

ex 

%sysExec move "&dir\%qsysfunc(dread(&did,&i)" "&newdir\%qsysfunc(dread(&did,&i)" /E

New Contributor
Posts: 4

Re: Combine multiple imported files into one data set

thanks for the reply Jennys.

 

Doing what you've suggested means I have a default table setup, kind of a master table with the column headers in place (persumably?)

 

Now my question is how do I get SAS to skip the top row of each file so I can add on the data and not the headings. 

 

unfortunately i cant run x commands, this is at work and my privelges dont extend that far.

Super User
Posts: 8,069

Re: Combine multiple imported files into one data set

Although the files have the same columns by name, I would still refrain from using proc import, and pull one proc-import-generated data step from the log and use that (after adapting to the file specifications). Otherwise you will have problems with differing variable attributes one day.

Keep in mind that each time proc import runs, it has to make guesses about data structure. Different data will lead to different structures for the individual datasets, which can (will) cause problems when concatenating/appending.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Combine multiple imported files into one data set

Posted in reply to KurtBremser

Hi Kurt, appreciate you replying.

 

apologies but what do you mean by :

pull one proc-import-generated data step from the log and use that (after adapting to the file specifications).

 

you said refrain from using proc import. what's the alternative?

 

the second part of your reply, are you saying to identify the columns? e.g. data, characters, numbers, dates etc and have that within the code rather than leaving up to SAS to decide? 

 

thanks

Super User
Posts: 8,069

Re: Combine multiple imported files into one data set

[ Edited ]

Dolmio wrote:

Hi Kurt, appreciate you replying.

 

apologies but what do you mean by :

pull one proc-import-generated data step from the log and use that (after adapting to the file specifications).

 

you said refrain from using proc import. what's the alternative?

 

the second part of your reply, are you saying to identify the columns? e.g. data, characters, numbers, dates etc and have that within the code rather than leaving up to SAS to decide? 

 

thanks


proc import makes (educated) guesses about the data structure by reading a limited amount of rows (that's the guessingrows= value) and then (in the case of text-based files) creates a data step that reads the data. This data step is found in the log.

(Since proc import does not (cannot) create a data step for Excel files, this is one of the reasons why we here STRONGLY advise against the use of Excel files for data import into SAS. See Maxims 22, 27 and 31)

 

Imagine a situation where the first csv file you read has only a maximum length of 20 for the values in a given character variable within the inspected lines, although that column is specified to hold 40 characters. Values further down in the file may be truncated, and when you concatenate multiple files, you may lose up to half of the data. Therefore I advise you not to use proc import in a production-type scenario.

 

But you can use proc import once to get the blueprint of the data step (just copy it from the log), and can then adapt that data step to the specifications you were given along with the data files. This data step will then create consistent structures across all imported datasets, and you'll have no problems when concatenating them.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: Combine multiple imported files into one data set

Posted in reply to KurtBremser

Thanks so much for this Kurt. Although i dont have a solution just yet, I have finally understood what you actually meant by that.

 

The maxims are great btw.

 

Thanks again, appreciate your replies.

 

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 245 views
  • 0 likes
  • 3 in conversation