Date:         Wed, 1 Nov 2006 18:40:25 -0500
Reply-To:     "Droogendyk, Harry" <harry.droogendyk@RBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Droogendyk, Harry" <harry.droogendyk@RBC.COM>
Subject:      Re: reading in multple csv files and exporting to excel via macro
In-Reply-To:  <c2192a610611011525m214b2aafx47e00cb4fe9bc895@mail.gmail.com>
Content-Type: text/plain; charset="iso-8859-1"

Pipe the results from a DOS dir command into a data step, creating a
macro variable for each filename found.  Within a macro loop, execute
the proc import using the macro variable as input.  An example follows:

filename web pipe "dir &data\web\&bu\*.csv /b";

%let flmt = 0;

data _null_;
        infile web end = done;
        input;
        put _infile_;
        call symput('f'||put(_n_,3.-l),trim(_infile_));
        if done then call symput('flmt',put(_n_,3.-l));
run;

%macro import_web;

    proc datasets lib = work nolist;
                delete web;
        quit;

        /*  Establish field lengths in case first CSV has short
directory names  */

        data web;
                length main_directory
                           sub_directory  $50
                           campaign_cd    $20
                           web_resp_cnt
                           response_date  8
                           ;
                stop;
        run;

        %do i = 1 %to &flmt;
                proc import datafile = "&data\web\&bu\&&f&i"
                                             out = csv
                                            dbms = csv replace;
                                        getnames = yes;
                run;

                proc append base = web
                                        data = csv force;  /* field
lengths differ from file to file */
                run;
        %end;

%mend import_web;

%import_web;

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of SAS_learner
Sent: 2006, November, 01 6:26 PM
To: toby dunn
Cc: SAS-L@listserv.uga.edu
Subject: Re: reading in multple csv files and exporting to excel via
macro

I think

you can write  an VB macro to pick up names of all the files (ending
with an extension in one folder)  into an excel sheet and then make that
list of files to go into macro list (using proc Sql )  and feed  that
macro into proc import, (well if everything is on Windows envirnorment )

I do not know how to get list of files in a folder in other operating
systems, I am sure there must be a way .

thanks





On 11/1/06, toby dunn <tobydunn@hotmail.com> wrote:
>
> Neerav ,
>
> Pipe all the file names into a SAS data set.  Then use filevar option
> on teh infile statement to get your data into SAS or use a data _Null_

> step and call execute to create the import/export code.
>
>
>
> Toby Dunn
>
> The obscure we see eventually.  The completely obvious, it seems,
> takes longer.  ~Edward R. Murrow
>
>
> Think like a man of action, act like a man of thought.  ~Henri Louis
> Bergson
>
>
>
> Alice came to a fork in the road.  "Which road do I take?" she asked.
> "Where do you want to go?" responded the Cheshire cat.
> "I don't know," Alice answered.
> "Then," said the cat, "it doesn't matter."
> ~Lewis Carroll, Alice in Wonderland
>
>
>
>
>
>
> From: Neerav Monga <neerav.monga@GMAIL.COM>
> Reply-To: Neerav Monga <neerav.monga@GMAIL.COM>
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: reading in multple csv files and exporting to excel via macro
> Date: Wed, 1 Nov 2006 14:19:39 -0800
>
> 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
>
> _________________________________________________________________
> Stay in touch with old friends and meet new ones with Windows Live
> Spaces
>
> http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http
>
://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&
mkt=en-us
>
_______________________________________________________________________

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations.
Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized.
If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.
Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite.
Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.