Online File

How to use this page

---

Rick Aster: Professional SAS Programming Shortcuts: Contents

Chapter 12
Program
Exporting to spreadsheet with labels as headers

---

DATA _NULL_;
   LENGTH TYPE $ 1 FMT $ 32;
   * Labels, character values, and fields are limited to these maximum lengths.;
   LENGTH LABEL $ 40 CVAL FIELD $ 256;
   * Output text file.;
   FILE OUT DLM='09'X LRECL=8192;
   * Input SAS dataset.;
   DSID = OPEN('WORK.MYDATA');
   * Write labels of variables.;
   NVARS = ATTRN(DSID, 'NVARS');
   IF _N_ = 1 THEN DO N = 1 TO NVARS;
      LABEL = VARLABEL(DSID, N);
      IF LABEL = '' THEN LABEL = VARNAME(DSID, N);
      PUT LABEL @;
      END;
   PUT ;
   * Read and write data.;
   NOBS = ATTRN(DSID, 'NLOBS');
   DO I = 1 TO NOBS;
      RC = FETCH(DSID);
      IF RC THEN LEAVE;
      DO N = 1 TO NVARS;
         TYPE = VARTYPE(DSID, N);
         FMT = VARFMT(DSID, N);
         LEN = VARLEN(DSID, N);
         SELECT (TYPE);
            WHEN('C') DO;
               IF FMT = '' THEN FMT =
                   '$CHAR' || TRIM(LEFT(PUT(LEN, F5.))) || '.';
               CVAL = GETVARC(DSID, N);
               FIELD = PUTC(CVAL, FMT);
               END;
            WHEN('N') DO;
               IF FMT = '' THEN FMT = 'BEST12.';
               NVAL = GETVARN(DSID, N);
               FIELD = PUTN(NVAL, FMT);
               END;
            END;
         PUT FIELD @;
         END;
      PUT ;
      END;
   RC = CLOSE(DSID);
   STOP;
RUN;

*
  To use this program, change the input SAS dataset name.
  On an EBCDIC system, change the DLM= option to DLM='05'X.
*;

Alternate version

data _null_;
   length type $ 1 fmt $ 32;
   * Labels, character values, and fields are limited to these maximum lengths.;
   length label $ 40 cval field $ 256;
   * Output text file.;
   file out dlm='09'x lrecl=8192;
   * Input SAS dataset.;
   dsid = open('WORK.MYDATA');
   * Write labels of variables.;
   nvars = attrn(dsid, 'NVARS');
   if _n_ = 1 then do n = 1 to nvars;
      label = varlabel(dsid, n);
      if label = '' then label = varname(dsid, n);
      put label @;
      end;
   put ;
   * Read and write data.;
   nobs = attrn(dsid, 'NLOBS');
   do i = 1 to nobs;
      rc = fetch(dsid);
      if rc then leave;
      do n = 1 to nvars;
         type = vartype(dsid, n);
         fmt = varfmt(dsid, n);
         len = varlen(dsid, n);
         select (type);
            when ('C') do;
               if fmt = '' then fmt =
                   '$CHAR' || trim(left(put(len, f5.))) || '.';
               cval = getvarc(dsid, n);
               field = putc(cval, fmt);
               end;
            when ('N') do;
               if fmt = '' then fmt = 'BEST12.';
               nval = getvarn(dsid, n);
               field = putn(nval, fmt);
               end;
            end;
         put field @;
         end;
      put ;
      end;
   rc = close(dsid);
   stop;
run;

*
  To use this program, change the input SAS dataset name.
  On an EBCDIC system, change the DLM= option to DLM='05'X.
*;