Date:         Fri, 29 Mar 1996 18:59:18 -0500
Reply-To:     AdamHndrx <adamhndrx@aol.com>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         AdamHndrx <adamhndrx@AOL.COM>
Organization: America Online, Inc. (1-800-827-6364)
Subject:      Utility Macro: SAS Dataset or View --> ASCII

This is a SAS macro utility that extracts a tab delimited flat file from a
SAS dataset or view.  The only parameters needed are the libname and the
object name.  Another simple program has also been included that can be
used to
extract a whole library of datasets to tab delimited flat files.  Tab
delimited
flat files can be easily imported to EXCEL.  Detailed info has been
included in
the macro header.  I hope this is useful... :-)
 
Adam Hendricks
ICOS Corporation
Bothell, WA
 
------------------------------------ cut
-----------------------------------
 
%* SAS Macro Program
%*
%* SAS Version: 6.11 on SunOS 4.1.3 (UNIX)
%*
%* Name: flatout
%*
%* Usage: %flatout(<SAS libname>, <SAS dataset or view>)
%*
%* Global Macrovariables Generated: none
%*
%* Parameters: 1 - Valid SAS libname (Not required for WORK dataset or
view)
%*             2 - Valid SAS dataset or view name (Required)
%*
%* Function: Generates a tab delimited flat file from a valid SAS dataset
or
%*           view.  Flat file is created in default directory under the
same
%*           name as the dataset or view (lowercase in UNIX) with the
extension
%*           '.txt'.  Generates a SAS listing containing the following
%*           information for the output flat file:
%*
%*             1. Column Number
%*             2. SAS Column Name
%*             3. SAS Column Label
%*
%* Limitations: - This *should* work on versions 6.07 or later on UNIX,
Windows
%*                or OS/2 platform.  Should work on MVS if libraries are
%*                allocated with LIBNAME statements and not JCL or TSO
%*                statements.
%*
%*              - Tested on SAS v6.11 on SunOS 4.1.3 (UNIX) only.
%*
%*              - Will overwrite any file of the same name as is described
in
%*                'Function:' section above.
%*
%*              - Will generate an ERROR if unknown format is used in
dataset
%*                or view AND the option FMTERR is in effect.
%*
%*              - Will output dates and times in numeric format if proper
%*                format not applied to variable in source dataset.
%*
%*              - Writes out one line per observation.  Subject to OS
limits on
%*                record length.
%*
%*              - To get the full benefit of the macro, explanatory labels
should be
%*                added to all columns in the dataset.
%*
%* Programmer: Adam Hendricks, ahendric@icos.com, (206) 485-1900, ext.
2295
%*
%* Date: 2/22/96
%*
%* Update:
%*;
%macro flatout(lib, dsn);
 
%* Declare local macrovariables *;
%local loclindx lib dsn fn;
 
* Error checking *;
data _null_;
  * Grab input parameter to variable *;
  lib = compress("&lib");
 
  * Default to WORK libname if none specified *;
  if lib = ' '
  then call symput('lib','WORK');
run;
 
* Check to see if dataset or view actually exists. *;
* Call error if not found.                         *;
proc sql noprint;
  validate
  select *
  from &lib..&dsn;
 
* Generate Local Macrovariables *;
data _null_;
  length lib dsn $8;
  lib = upcase(compress("&lib"));
  dsn = upcase(compress("&dsn"));
  call symput('lib', compress(lib)); * Libname *;
  call symput('dsn', compress(dsn)); * Dataset or View *;
  call symput('fn', compress(lowcase(dsn))); * Filename w/o extension *;
run;
 
* Check number of observations in dataset or view. *;
* Call error if no observations found.             *;
proc sql noprint;
  select count(*) into :nobs
  from &lib..&dsn;
 
%if %eval(&nobs) = 0
%then %do;
data _null_;
  error "ERROR: SAS dataset or view &lib..&dsn has no observations.";
run;
%end;
 
proc sql noprint;
  create table metadata as
  select varnum, name, format, label
  from dictionary.columns
  where libname = "&lib" and
        memname = "&dsn"
  order by 1;
 
%let vars = &sqlobs;
 
proc print data=metadata label noobs;
  title1 "Layout for Tab Delimited Text SAS Extract '&fn..txt'";
  title2 " ";
  title3 "Date: &sysdate  Time: &systime";
  var varnum name label;
  label name  = 'SAS Column Name'
        label = 'Column Label';
run;
 
* Generate macrovariable array for data _null_ put statement *;
data _null_;
  set metadata end=eof;
    by varnum;
  call symput('name'||left(varnum), compress(name));
  call symput('format'||left(varnum), compress(format));
run;
 
* Generate tab delimited flat file. *;
filename flat "&fn..txt";
data _null_;
  set &lib..&dsn;
  dlm = byte(9); * Tab ASCII Code *;
  file flat noprint;
  put
%do loclindx = 1 %to &vars;
    &&name&loclindx &&format&loclindx
  %if %eval(&loclindx) < %eval(&vars)
  %then dlm;
%end;
;
run;
%mend;
 
------------------------------------ cut
-----------------------------------
 
Here is a sample program that uses the previous macro to extract a whole
library of SAS datasets into matching tab delimited flat files:
 
 
%include 'flatout.sas'/nosource2;
options errorabend macrogen ls=128 ps=42;
proc sql;
  create table tables as
  select libname, memname
  from dictionary.tables
  where libname eq '<libname>' <--- edit here.
  order by 1,2;
 
%let ntables = &sqlobs;
 
data _null_;
  set tables;
    by libname memname;
  call symput('lib'||left(_n_), compress(libname));
  call symput('dsn'||left(_n_), compress(memname));
run;
 
%macro runit;
%do i = 1 %to &ntables;
  %flatout(&&lib&i, &&dsn&i)
%end;
%mend;
 
%runit