Date:         Wed, 18 Jun 2008 13:36:56 -0500
Reply-To:     "data _null_," <datanull@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "data _null_," <datanull@GMAIL.COM>
Subject:      Re: export macro problem
Comments: To: adjgiulio <adjgiulio@libero.it>
In-Reply-To:  <211c32cb-7cc7-4f03-a42b-2b7779d92d22@z66g2000hsc.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

Use a FILEREF with LRECL specification.

filename FT55F001 'Path' / lrecl=1024 /*or more*/;

%makefile(dataset=data,
         filename=FT55F001,   /* FILEREF or DDNAME of the file */
         dlmr=",",
         quote="yes",
         header="yes",
         label="yes");


On 6/18/08, adjgiulio <adjgiulio@libero.it> wrote:
> Hi,
>
> I'm using the code below to export a SAS dataset to a .txt file, comma
> delimited, with " as text identifier, and using lables as headers.
> This is a macro I found on SAS website.
> Each observation includes ~30 variables.
> The problem I'm having is that on the .txt output file each
> observation is being broken down in multiple rows. This will cause
> problems to those who will import the .txt file with software other
> than SAS.
> Any ideas on how this macro could be modified so that every SAS
> observation is placed on a single row on the .txt file?
>
> Thanks,
>
> G
>
>
> /*******************************************************************/
> /*  Title :  Robust macro to write delimited file                  */
> /*                                                                 */
> /*  Goal  :  Create a comma delimited text file from a SAS data    */
> /*           set using macro functionality.  Several parameters    */
> /*           can be used to generate a header, use variable        */
> /*           labels, and specify the delimiter.                    */
> /*                                                                 */
> /*******************************************************************/
>
> options mprint;
>
> %macro makefile
>  (
>   dataset=_last_ ,  /* dataset to write */
>   filename=print ,  /* file to write to */
>   dlmr=","       ,  /* delimiter between values */
>   quote="no"     ,  /* should sas quote all character variables? */
>   header="no"    ,  /* do you want a header line w/ column names? */
>   label="no"        /* should labels be used instead of var names in
> header? */
>  );
>
> proc contents data=&dataset out=___out_;
> run;
>
> /* return to orig order */
>
> proc sort data=___out_;
>  by varnum;
> run;
>
> /* build list of variable names */
>
> data _null_;
>  set ___out_ nobs=count;
>  call symput("name"!!left(put(_n_,3.)),name);
>  call symput("type"!!left(put(_n_,3.)),type);
>  /* use var name when label not present */
>  if label=" " then label=name;
>  call symput("lbl"!!left(put(_n_,3.)),label);
>  if _n_=1 then call symput("numvars", trim(left(put(count, best.))));
>
> /* Create file */
>
> data _null_;
>  set &dataset;
>  file &filename;
>  %global temp;
>  %if &quote="yes" %then %let temp='"';
>  %else %let temp=' ';
>  %if &header="yes" %then %do;
>    /* conditionally add column names */
>    if _n_=1 then do;
>        put %if &label="yes" %then %do;
>        %do i=1 %to &numvars-1;
>          &temp  "%trim(&&lbl&i) " +(-1) &temp &dlmr
>          %end;
>        &temp "%trim(&&lbl&numvars) " &temp;
>        %end;
>    %else %do;
>      %do i=1 %to &numvars-1;
>        &temp "%trim(&&name&i) " +(-1) &temp &dlmr
>        %end;
>       &temp "%trim(&&name&numvars) " &temp ;
>       %end;
>    ;
>    end;
>  %end;
>
>  /* build PUT stmt to write values */
>  put
>     %do i = 1 %to &numvars -1;
>       %if &&type&i ne 1 and &quote="yes" %then %do;
>         '"' &&name&i +(-1) '"' &dlmr
>         %end;
>       %else %do;
>         &&name&i +(-1) &dlmr
>         %end;
>     %end;
>     %if &&type&i ne 1 and &quote="yes" %then %do;
>       /* write last varname*/
>       '"' &&name&numvars +(-1) '"';
>       %end;
>       %else %do;
>         /* write last varname*/
>         &&name&numvars;
>       %end;
>  run;
> %mend makefile;
>
> /* If LRECL= required because of records longer the 256, specify here
> */
>
> filename myfile "~/tmp/rawdata" lrecl=5;
>
>
> /* Invoke macro to write to a file, include proper parameters for your
> case.
>   Make sure that the variables are in the order you want and have the
>  desired formats.
> */
>
> run;
>