Date:         Fri, 14 Jun 2002 10:43:28 -0400
Reply-To:     Steve Bloom <steve@WHITEHURST-ASSOCIATES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Steve Bloom <steve@WHITEHURST-ASSOCIATES.COM>
Subject:      Re: Creating header row for .csv file
Comments: To: "Goldman, Brad (AT-Atlanta)" <Brad.Goldman@AUTOTRADER.COM>
In-Reply-To:  <2AE36192DC1E24479B195A804DE811B53D6913@at0exc00.autoconnect>
Content-Type: text/plain; charset="iso-8859-1"

SAS's website has a macro that will create a CVS file for SAS/INTRNET.
Character variables are quoted, and numeric values are not. It also puts the
variable names in the first row. I've slightly modified the macro to create
a file.

Save the macro in \core\sasmacro\ subdirectory and all you will ever need to
do to create a CVS file is to add one line code:
                %csvfile(work, data1, "c:\temp\temp.cvs");

%macro csvfile(libname,dsname,cvsfile);

  proc sql noprint;
    create view VARLIST as select format, label, name, type
      from dictionary.columns
      where libname="%upcase(&LIBNAME)" and memname="%upcase(&DSNAME)";
    select count(name) into: NUMVARS from dictionary.columns
      where libname="%upcase(&LIBNAME)" and memname="%upcase(&DSNAME)";

/* now use the data step symput call routine to move all the
     variable formats, names and labels from data step view variables to
     macro variables */


  data _null_;
    set work.varlist;

    call symput('frmt'||left(put(_n_,4.)),trim(format));
    call symput('labl'||left(put(_n_,4.)),trim(label));
    call symput('name'||left(put(_n_,4.)),trim(name));
    call symput('type'||left(put(_n_,4.)),trim(type));
  run;

  data _null_;

    /*  first set in the dataset you want to write in .csv format */
    /*  &LIBNAME and &DSNAME come straight from the macro parameters */

    set &LIBNAME..&DSNAME;

    file &cvsfile;

    if _n_ = 1 then do;
         put
          %do i = 1 %to &NUMVARS;
            %if &&labl&i.= %then
              "&&name&i." ;
            %else
              "&&labl&i." ;
            %if &i ^= %left(%trim(&NUMVARS)) %then ", ";
          %end;
        ;
    end;

/*  now create a put statement with the variable names and the formats
    for each variable */

    put
      %do i = 1 %to &NUMVARS;

        %if %upcase(&&frmt&i..) = AGEF. %then %do;
           '"' &&name&i.. &&frmt&i..
           %if &&frmt&i.. = %then +(-1);
           '"'
       %end; %else %do;
               &&name&i.. &&frmt&i..
           %if &&frmt&i.. = %then +(-1);
       %end;

       %if &i ^= %left(%trim(&NUMVARS)) %then ", ";

      %end;
    ;
        run;
%mend csvfile;





-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
Goldman, Brad (AT-Atlanta)
Sent: Thursday, June 13, 2002 2:13 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Creating header row for .csv file


Some time ago, someone posted a great snippet of code for producing .csv
files:

data _null_;
 set    your.dataset;
 file "<your output file.csv>" lrecl=32000 dsd ;
 put (_all_)(:) ;
run;

This is perfect for me, except I would also like to have the header row with
variable names.  I have tried a dozen variants of the following, and can't
seem to get it to work.

proc sql;
select name
into: vnames separated by ', '
from dictionary.columns
where libname="WORK"
  and memname="FD2" ;
quit;

data _null_;
file "/links/mandash/dealer_services/ford/brad.csv" lrecl=32000 dsd;
set fd2;
if _N_=1 then put "&vnames";    * <--- here be trouble;
  put (_all_)(:) ;
run;

The problem is that wretched macro reference.  It is put out to the external
file, but it is also interpreted.  If I don't have it in quotes, I get error
messages.  If it is in quotes, then Excel cannot parse out the comma
separated fields since they're within quotes.  If I change it to an %if
%then construction, the _N_=1 is never true.  I can't seem to find the right
quoting function, if there is one.  Any help is appreciated.  Patronizing
lectures on compilation and execution time also welcome. :)

-Brad