BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tom
Super User Tom
Super User

I would suggest ditching the complex macro code to get the list of filenames.

My preference in order would be

1) Just use a wildcard in the filename you reference in the INFILE statement so that SAS will find the files for you.

2) Use operating system command like ls or dir with a pipe to read the filenames.

data files; infile "dir ""&mydir\BTLT4-*.csv"" /b" pipe truncover; input filename $256. ; run;

3) If you really must use the functions like DOPEN then do it in an actual DATA step where you can put in some PUT statements or even use the SAS datastep debugger to see what is happening.

UT_Risom
Calcite | Level 5

I was able to create a macros to compile the list of files in my directory, just to see if that was the issue.

/*compiled directory*/

%macro get_filenames(location);

   filename pipedir pipe "dir ""%unquote(&location)"" /b";

   data files;

     infile pipedir truncover;

     input filename $256.;

     put filename=;

   run;

   filename pipedir clear; 

%mend;

%get_filenames(C:\Users\Owner\Desktop\SAS Project\ACL CSV files)

Unfortunately I'm still running into the same issue with the previous syntax, even after adjusting the infile statement. Are there any examples of alternative syntax (with or without macros) you recommend looking at, or if your willing to show me how to arrange what I have in a way that might be more efficient?

- SMR

/*adjusted syntax*/

options mprint;

%macro MultImp(dir=,out=);

  %let rc=%sysfunc(filename(mydir,&dir));

  %let did=%sysfunc(dopen(mydir));

%put dir=&dir;

%put did=&did;

  %let filrf=MYDIR;

  %let lstname=;

  %let memcount=%sysfunc(dnum(&did));

  %if &memcount > 0 %then %do;

  %do i=1 %to &memcount;

  %let lstname=%sysfunc(dread(&did,&i));

  %let file=&dir.&lstname;

data files;

  infile "dir ""&mydir\BTLT4-*.csv"" /b" pipe truncover;

  input filename $256.;

  run;

  proc append data=_&i base=&out; run;

  proc delete data=_&i; run;

  %end;

  %let rc=%sysfunc(dclose(&did));

  %end;

  %mend MultImp;

  %MultImp(dir=C:\Users\Owner\Desktop\SAS Project\ACL CSV files\,out=File);

Reeza
Super User

data files;

  infile "dir ""&mydir\BTLT4-*.csv"" /b" pipe truncover;

  input filename $256.;

  run;

  proc append data=_&i base=&out; run;

  proc delete data=_&i; run;

  %end;

This part doesn't look correct.

Should it be data &out or _&i or something else. Using a Static name with the wild card doesn't make sense after you've inputted all the file names as well.

Use the solution posted by Data _null_ above.

UT_Risom
Calcite | Level 5

I tried the solution posted by Data _null_ above. Still the same issue.

- SMR

Tom
Super User Tom
Super User

???Why do you still have all of that macro stuff with %DO loops and SYSFUNC() calls if you are going to read the output of the DIR command???

???Where is the code to actually read the individual CSV files into datasets???

Tom
Super User Tom
Super User

I really do not recommend running PROC IMPORT on a series of CSV files because the results are too unpredictable.  The length and even the type of the resulting variable can be very different depending on the actual values that happen to be in the particular file.

But here is a simple macro to use the output of the DIR/B command to generate multiple PROC IMPORT/PROC APPEND calls.

%macro multimp(dir=,out=);

* Make sure output ds does not exist ;

proc delete data=&out; run;

* Read list of filenames and generate PROC IMPORT and PROC APPEND for each one ;

filename code temp ;

data _null_ ;

  infile "dir ""&dir\btlt4-*.csv"" /b" pipe truncover;

  input filename $256.;

  file code ;

  put 'proc import datafile="&dir\' filename +(-1) '" out=onefile replace;'

    / 'run;'

    / 'proc append data=onefile base=&out force; run;'

  ;

run;


* Run the generated code ;

%inc code / source2 ;

%mend multimp ;


Chang
Quartz | Level 8

Thanks. Tom. This is really helpful.

Kurt_Bremser
Super User

Hi!

If you try to open every file (of an unknown number of files) by itself, you may run out of available file handles (limit depending on the operating system).

In cases like this, I prefer to concatenate all the files with the help of the OS so I only have to read one file in SAS.

(eg UNIX "cat xxxx* > xxxx.concat").

But then, I have the blessing of running SAS on a Unix instead of Windows.

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!

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
  • 22 replies
  • 10079 views
  • 2 likes
  • 9 in conversation