Date:         Fri, 30 Jan 2009 06:25:59 -0600
Reply-To:     "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Subject:      Re: Import CSV file to SAS
Comments: To: "Howard Schreier <hs AT dc-sug DOT org>"
          <schreier.junk.mail@gmail.com>
In-Reply-To:  <200901300255.n0TN5jxT013236@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

In this case, where to me, the main point is to take advantage of the
FIRSTOBS INFILE statement option is to use a different set of INFILE
statement options.  Namely PIPE and FILEVAR.

filename FT55F001 '.';
data all(keep=source clientName Protocol ShortName StatusID StudyID);
   length csvname path command $256 source $16;
   path = catx('\',pathname('FT55F001'),'murugesh*.csv');
   *command = catx(' ','dir /b/s',quote(strip(path)));
   command = catx(' ','attrib ',quote(strip(path)));

   infile dummy1 pipe filevar=command truncover;
   *input csvname $256.;     *with DIR;
   input @11 csvname $256.; *with ATTRIB;
   infile dummy2 filevar=csvname eof=eof firstobs=3 dsd;
   source = scan(scan(csvname,-1,'\'),1,'.');
   do while(1);
      informat ClientName  $50.  Protocol $8. shortname $8.;
      input ClientName   Protocol   ShortName StatusID StudyID;
      output;
      end;
 eof:
   list;
   run;

On 1/29/09, Howard Schreier <hs AT dc-sug DOT org>
<schreier.junk.mail@gmail.com> wrote:
> On Thu, 29 Jan 2009 06:19:30 -0500, Nat Wooding <Nathaniel.Wooding@DOM.COM>
> wrote:
>
> >The code in the reply below will not work for several reasons so I have
> >adjusted it a bit:
> >
> >Data csv;
> >
> >infile CARDS  /* i just put the data here for convenience */
> >firstobs=3
> >dlm=',' dsd missover;
> >
> >informat ClientName  $50.  Protocol $ 8. shortname $ 8.;
> >input ClientName   Protocol   ShortName StatusID StudyID ;
> >cards;
> >COL1,COL2,COL3,COL4,COL5
> >ClientName,Protocol,ShortName,StatusID,StudyID
> >ABC co ltd,ABC-3001,ABC3001,3,2314
> >DEF , D 123, D123, 4, 2256
> >RUN;
> >PROC PRINT;
> >RUN;
> >
> >
> >There should not be commas in the input statement (This was the result, I
> >assume, of cutting and pasting)
> >
> >The length followed the input statement but by that time, the SAS
> >supervisor had already set protocol and shortname to be numeric variables
> >since there was no other information.  I changed this to an informat and
> >placed it before the input statement.
> >
> >Now, in these 100 CSVs, are there always the same variables and are they in
> >the same order?
> >
> >
> >Nat Wooding
> >Environmental Specialist III
> >Dominion, Environmental Biology
> >4111 Castlewood Rd
> >Richmond, VA 23234
> >Phone:804-271-5313, Fax: 804-271-2977
> >
> >
> >
> >             murugesh
> >             <iammurugesh@GMAI
> >             L.COM>                                                     To
> >             Sent by: "SAS(r)          SAS-L@LISTSERV.UGA.EDU
> >             Discussion"                                                cc
> >             <SAS-L@LISTSERV.U
> >             GA.EDU>                                               Subject
> >                                       Re: Import CSV file to SAS
> >
> >             01/29/2009 04:09
> >             AM
> >
> >
> >             Please respond to
> >                 murugesh
> >             <iammurugesh@GMAI
> >                  L.COM>
> >
> >
> >
> >
> >
> >
> >On Jan 29, 8:05 am, "sukumarbalusw...@gmail.com"
> ><sukumarbalusw...@gmail.com> wrote:
> >> Hi,
> >>
> >> I'm having 100 CSV files.with the following 5 columns.
> >>
> >> Please help me how to import these, The first row is not important for
> >> me. I want to have the second row as a "Variable Name" in SAS Data
> >> Set. And my data starts from the 3rd row.
> >>
> >> eg of one CSV file.
> >>
> >> COL1,COL2,COL3,COL4,COL5
> >> ClientName,Protocol,ShortName,StatusID,StudyID
> >> ABC co ltd,ABC-3001,ABC3001,3,2314
> >> DEF , D 123, D123, 4, 2256
> >>
> >> Thanks in advcance.
> >>
> >> Regards,
> >> Sukumar
> >
> >Hi Sukumar,
> >
> >Please use the following code
> >
> >data ex1;
> >infile 'C:\Documents and Settings\mp31795\Desktop\model.csv'
> >firstobs=3 lrecl=32767
> >dlm=',' dsd missover;
> >input ClientName $ ,Protocol,ShortName;
> >length ClientName  $50.  Protocol 8. shortname 8.;
> >run;
>
> Nat's code can be extended to process all 100 files. Test data:
>
>   data _null_;
>   file 'murugesh101.csv';
>   put 'COL1,COL2,COL3,COL4,COL5';
>   put 'ClientName,Protocol,ShortName,StatusID,StudyID';
>   put 'ABC co ltd,ABC-3001,ABC3001,3,2314';
>   put 'DEF , D 123, D123, 4, 2256';
>   file 'murugesh202.csv';
>   put 'COL1,COL2,COL3,COL4,COL5';
>   put 'ClientName,Protocol,ShortName,StatusID,StudyID';
>   put 'GHI , GHI 999, GHI999, 9, 9999';
>   run;
>
> Code:
>
>   data all;
>   length csvname $100 source $ 15;;
>   infile 'murugesh*.csv' firstobs=3 dsd missover
>          eov=boundary filename=csvname;
>   boundary = 0;
>   input @;
>   if boundary or lag(boundary) then delete;
>   source = scan(scan(csvname,-1,'\'),1,'.');
>   informat ClientName  $50.  Protocol $ 8. shortname $ 8.;
>   input ClientName   Protocol   ShortName StatusID StudyID ;
>   run;
>
> Result:
>
>                                                      Status   Study
>     source      ClientName   Protocol   shortname     ID       ID
>
>   murugesh101   ABC co ltd   ABC-3001    ABC3001       3      2314
>   murugesh101   DEF          D 123       D123          4      2256
>   murugesh202   GHI          GHI 999     GHI999        9      9999
>