Date:         Fri, 3 Nov 2006 12:40:11 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: reading in multple csv files and exporting to excel via macro

On Wed, 1 Nov 2006 14:19:39 -0800, Neerav Monga <neerav.monga@GMAIL.COM> wrote:

>Hi everyone,
>I have about 50 files that are in csv format labelled file1.csv ,
>file2.csv etc... (however it does skip numbers, eg.. 1-5, then 20-25,
>they aren't necessarily consecutive numbers). I saved all of them in
>one folder in windows xp using SAS 9.1.3.
>
>I know i can put the proc import/export into a macro and feed the macro
>the input filename and export file name, however, is there a way to
>automate this somehow? It would save me a lot of time.
>
>I know the non consecutive numbers might be an issue, but they have to
>stay as labelled. After the file is imported, I need to remove the
>first four characters in a field in the file (which I can easily do
>with the substring command), drop the old field with extra characters
>and then export it.
>
>Perhaps there's a way to import / export an entire folder/library at
>once? That might be the easiest way to go?
>
>Any help would be appreciated,
>
>TIA,
>
>Neerav

Peter pointed out that PROC IMPORT often fails to deal with common data
situations. If the files are "nice" and don't pose such problems, here is a
no-macro way to automate PROC IMPORT.

Test data:

   data _null_;
   file 'myfile1.csv';
   put 'whatever' / '1' / '1.1';
   file 'myfile2.csv';
   put 'whatever' / '2' / '2.2';
   run;

The following step spawns all of the nedded PROC IMPORT steps, via CALL EXECUTE:

   data _null_;
   length dsn code $ 100;
   infile 'myfile*.csv' filename=dsn;
   input;
   dsn = scan(scan(dsn,-1,'\'),1,'.');
   if dsn ^= lag(dsn);
   code = 'PROC IMPORT OUT=one' ||
          ' DATAFILE="' || trim(dsn) || '.csv" ' ||
          'DBMS=CSV REPLACE; ' ||
          'GETNAMES=YES; ' ||
          'DATAROW=2; ' ||
          'RUN;' ;
   call execute(code);
   if _n_=1 then call execute('data all; set one; run;');
   else call execute('proc append base=all data=one; run;');
   run;

The keys are in the INFILE: the wildcard (*) and the FILENAME= option. We
are passing all of the data just to get the file names. That's not a good
idea in general, but here the complete concatenation is to go into one Excel
spreadsheet, so the files cannot be very large.

The PROC APPEND code, also generated via CALL EXECUTE, concatenates the data
sets.

A DATA step can follow, to make the transformations needed.

Finally, export:

   PROC EXPORT DATA= all OUTFILE= "myxlout.xls" DBMS=EXCEL REPLACE;
   RUN;