Date:         Tue, 7 Aug 2007 18:09:35 -0400
Reply-To:     Matt Pettis <matt.pettis@THOMSON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Matt Pettis <matt.pettis@THOMSON.COM>
Subject:      Re: import very wide csv
Comments: To: Bill Howells <whowells@YAHOO.COM>

Try the following to turn your data into a long skinny table.

Let me know if you'd like me to email the .sas file to you directly (I don't
by default because I know most people have their spam filter on).

This can probably be done in one datastep rather than two, but the first one
only reads one record in, so it is O(1).  Any improvement on the code is
always welcome.

HTH,
Matt

====================================================
/*--------------------------------------------------------------------*\
  config
\*--------------------------------------------------------------------*/

/*
for:
http://listserv.uga.edu/cgi-bin/wa?A2=ind0708a&L=sas-l&D=1&O=D&P=50275
*/

/*Contents of file test.csv (i'm keeping in c:\temp*/

/*
a,b,c,d d,e e,f f,g g,h h
11,12,13,14,15,16,17,18
21,22,23,24,25,26,27,28
31,32,33,34,35,36,37,38
41,42,43,44,45,46,47,48
51,52,53,54,55,56,57,58
61,62,63,64,65,66,67,68
71,72,73,74,75,76,77,78
81,82,83,84,85,86,87,88
91,92,93,94,95,96,97,98
101,102,103,104,105,106,107,108
*/

/*--------------------------------------------------------------------*\
  import
\*--------------------------------------------------------------------*/

  /*Read in number of headers into macrovar '&no_vars'*/
data _null_;
  infile 'c:\temp\test.csv' lrecl=32767 dsd dlm=',' missover;
  do until (dummy = '');
    input dummy:$3. @;
    counter ++ 1;
    put dummy=;
  end;
  call symput('no_vars',counter - 1);
  stop;
  run;

  /*Read in Data*/
data work;
  infile 'c:\temp\test.csv' lrecl=32767 dsd dlm=',' missover;
  keep record_number header val;

    /* Read in and retain the headers*/
    /* For output of each row */
  array headers{&no_vars} $ _TEMPORARY_;
  if _n_=1 then do;
    do i=1 to dim(headers);
      input dummy:$3. @;
      headers(i) = dummy;
    end;

      /*Bare input statement allows pointer to go to next csv record*/
    input;
    return;
  end;

    /* All obs on same row will have same record_number*/
  record_number ++ 1;

    /* Read in the vals and output one row per val*/
  do i=1 to &no_vars;
    input val:$3. @;
    header = headers(i);
    output;
  end;

    /*Bare input statement allows pointer to go to next csv record*/
  input;

  run;
====================================================