BookmarkSubscribeRSS Feed
Dolmio
Calcite | Level 5

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.

 

 

6 REPLIES 6
Jennys
Calcite | Level 5

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

Dolmio
Calcite | Level 5

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.

Kurt_Bremser
Super User

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.

Dolmio
Calcite | Level 5

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

Kurt_Bremser
Super User

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

Dolmio
Calcite | Level 5

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.

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 3090 views
  • 0 likes
  • 3 in conversation