Mailer: Elm [revision: 66.33]
Newsgroups: bit.listserv.sas-l
Date:         Wed, 29 Jul 1992 22:56:52 MDT
Reply-To:     knechod@stacc.med.utah.edu
From:         Kevin Nechodom <knechod@STACC.MED.UTAH.EDU>
Subject:      Re: Comma delimited output file from SAS on MVS/ESA
Comments: To: SAS Mailing List <sas-l@uga.cc.uga.edu>
              Jul 29, 92 12:35 (noon)

>
> Hi Everyone,
>
> I am new to this list and I would appreciate any help if possible.
>
> I am trying to create a comma delimited flat file from SAS on an MVS machine.
> I know that I could put a ',' between each variable but I have 600 variables
> in 27 tables. I was wondering whether there is a shortcut? Any suggestions
> would be greatly appreciated.
>
>                  Teale Data Center, State of California
>                  Internet: DC.SYSD10@TS3.TEALE.CA.GOV
>                  Address: P.O. Box 13436, Sacramento, Ca. 95813
>                  phone: (916) 920-6568, atss 430-6568
>

I have enclosed two macros here.  The first is the text of a posting
by Ben Connor, who guided me in the right direction to Karen
Patterson, who solved just this very problem.  The second is my
rendition of a fax from Karen, with a couple of modifications.  1) I
needed column headers, and 2) I changed the macro to accept the
libref, filename, and output file.

Since I am NOT an IBM type of person, I have no idea about MVS, TSO,
etc.  Therefore, I can give no guarantees that these will work.
However, I have used it on an HP9000 HPUX machine using 6.07.02, and
it has worked wonderfully, with one qualification.  It may only be a
function of my ignorance, or a limitation of HPUX, but the file
generated had a 256 character maximum length.  Since my data file had
74 variables, of which many were long character strings, the output of
each data record was split into several output records.  Before I
could import the data file in my spreadsheet EXCEL, I had to piece
together each logical record.  That may, or may not, be a problem for
you, and I'm hoping someone on the list will suggest a fix to that
problem.

Finally, to voice an old complaint, it would be nice if the standard
implementation of SAS included 'standard' (in quotes in deference to
those who actually measure and evaluate such standards) file formats,
like CSV, DBF, DIF, etc.  With the proliferation of UN*X workstations
and the migratation of DOS programs like dBase to those workstations,
the need is becoming greater!

Anyway, to continue ...

From SAS-L@uga.cc.uga.edu Tue Jun 16 11:01 MDT 1992
Date:         Tue, 16 Jun 1992 09:56:00 PDT
From: Ben Conner <bconner@NETCOM.COM>
Subject:      Comma Delimited Files

> Based on the recent interest of Comma Delimited File formats, Karen Patterson
> of San Diego Data Processing Corporation gave me permission to post this to
> the list.  Hope it helps!
>   --Ben Conner
>   ---------------------------------------------------------------------------
> Below is the sample SAS macro code on the last page of a paper presented by
> Karen Patterson of San Diego Data Processing Corporation.  The paper was
 titled
> "Creating Comma-Delimited Files from SAS Data Sets for Downloading to Micro-
> computers".  Hope it helps...
>
> SAMPLE SAS MACRO CODE
>
> LIBNAME SASIN 'TSOXXX.SAS.DATA' DISP=SHR;
> FILENAME FLATOUT 'TSOXXX.SUGI17.FLATFILE' DISP=OLD REUSE;
>
> OPTIONS DQUOTE NOMPRINT NOMLOGIC NOMACROGEN NOSYMBOLGEN MISSING=' ';
>
> TITLE;
>
> %MACRO COMMA;
> * ASSIGN LITERALS TO MACRO VARIABLES;
> %LET LIBNAME=SASIN;
> %LET MEMNAME=SUGI17;
> %LET THEPUT=PUT;
>
> * SAVE CONTENTS OUTPUT IN A FILE;
> PROC CONTENTS DATA=&LIBNAME..&MEMNAME  OUT=FILEDEF NOPRINT;
>
> * SORT BY THE VARIABLE NUMBER;
> PROC SORT;
>   BY VARNUM;
>
> * PUT MACRO VARIABLES IN SYMBOL TABLE;
> DATA _NULL_;
>   SET FILEDEF;
>
> * NUMBER OF VARIABLES;
> CALL SYMPUT('NVAR',PUT(VARNUM,3.));
>
> * VARIABLE NAMES;
> CALL SYMPUT('VNAME'||LEFT(PUT(_N_,3.)),PUT(NAME,$8.));
>
> * TYPE = 1 IS NUMERIC;
> * TYPE = 2 IS CHARACTER;
> IF TYPE = 1 THEN
>   DO;
>   IF FORMAT = 'MMDDYY' THEN
>        CALL SYMPUT('TYPE'||LEFT(PUT(_N_,3.)),PUT("D",$1.));
>     ELSE
>        CALL SYMPUT('TYPE'||LEFT(PUT(_N_,3.)),PUT("N",$1.));
>   END;
> ELSE
> IF TYPE = 2 THEN
>   DO;
>     CALL SYMPUT('TYPE'||LEFT(PUT(_N_,3.)),PUT("C",$1.));
>   END;
>
> RUN;
>
> DATA _NULL_;
>
> * READ THE SAS DATASET;
> SET &LIBNAME..&MEMNAME;
>
> * CREATE A FLAT TSO DATASET;
> FILE FLATOUT;
>
> * POINTER VARIABLE TO MOVE COMMA BACK 1 COLUMN IN OUTPUT RECORD;
> P = -1;
>
> * USE MACRO VARIABLES TO BUILD A PUT STATEMENT;
>
> IF _N_ = 1 THEN
>   DO;
>     %DO I = 1 %TO &NVAR;
>        %IF "&&TYPE&I" = "D" OR
>            "&&TYPE&I" = "C" %THEN
>            %LET THEPUT = &THEPUT '"'&&VNAME&I +P '"' ',';
>        %ELSE
>            %LET THEPUT = %THEPUT &&VNAME&I +P ',';
>         %END;
>      END;
>
> * WRITE A COMMA DELIMITED RECORD;
> &THEPUT;
>
> RUN;
>
> %MEND COMMA;
>
> %COMMA;
>
 -------------------------------------------------------------------------------
> I've tried to be careful typing it in, but haven't had a chance to try it out
> to make sure all is well.
>    -Ben
> Southern California Edison
> (714) 368-8513
>

And with modification, and remembering that I know almost nothing
about SAS's macro language (or even what all these lines mean!):

%MACRO comma(lib,file,output);

filename SASOUT &output;
options dquote nomprint nomlogic nomacrogen nosymbolgen
  missing = ' ';

title;

%let THEPUT=PUT;
%let HDRPUT=PUT;

proc contents data=&lib..&file out=filedef noprint;
proc sort;
  by varnum;

data _NULL_;
  set filedef;

call symput('NVAR',put(varnum,3.));

call symput('VNAME'||left(put(_N_,3.)),put(name,$8.));

if type = 1 then
  do;
    if format="MMDDYY" then
      call symput('TYPE'||left(put(_N_,3.)),put("D",$1.));
    else
      call symput('TYPE'||left(put(_N_,3.)),put("N",$1.));
  end;
else
if type=2 then
  do;
    call symput('TYPE'||left(put(_N_,3.)),put("C",$1.));
  end;

run;

data _NULL_;

set &lib..&file;
file SASOUT;

p = -1;

if _N_ = 1 then
  do;
    %do i=1 %to &nvar;
      %let HDRPUT = &HDRPUT '"' "&&VNAME&I" '"' ',';
      %if "&&TYPE&I" = "D" or "&&TYPE&I"="C" %then
        %let THEPUT = &THEPUT '"' &&VNAME&I+P '"' ',';
      %else
        %let THEPUT = &THEPUT &&VNAME&I + P ',';
  %END;
&HDRPUT;
end;

&THEPUT;

run;

%MEND comma;

Sample run:
%comma(prod,airheum,aiout)



--
Kevin Nechodom UofU STACC        | Great Moments in Physics History:
 UofU doesn't even know I exist; | "E= m c cubed?  No, too gaudy.
 how would they care what I say? |  E= m c ?  No, too simple.
knechod@stacc.med.utah.edu       |  E= m c squared?  Ah, ha!"
Newsgroups: bit.listserv.sas-l