SAS Macros >> Try the new index page

by Richard A. DeVenezia

This collection of SAS macros is part of my SAS programming toolbox. They have been categorized into groups based on typical usage. They have not been peer tested for quality assurance and are presented as-is. Some macros may rely on host specific features. Use at your own risk. Send questions or feedback to Richard A. DeVenezia

Move data into SAS
Macro Purpose Notes
createDSN Programatically create an ODBC data source to a Microsoft Access database Will show up when you run ODBC Data Source Administrator control panel applet
Unprocessed submittal (#41) to the UGA Contribution Server. Readme ModuleN and Windows API. Updated 3/23/02.
Thanks to Lex Jansen for his bug report.
FindFiles Obtain information about files.
File information provided is path, name, type, size, time modified, altname and owner.
Windows only.
This macro will do wildcard and recursive searching.
Updated 11/03/02 to provide owner.
note2sas Read a Lotus Notes database that has been File/Exported... as a structured text file. All fields will be character
ora2sas Create SAS data sets or views from/to Oracle tables. Can reformat character columns based on first N rows.
pickdate New icon Select a date from a month long calendar.
quotes Fetch stock quotes from Yahoo into a data set. Past performance is not indicative of future profits :-)
read_dbf Import a dBase III file into a SAS data set. updated 11/13/00
Move data out of SAS
ds2htm Output data set as html. Modified version of SI macro to allow where clause to contain like operator with % match.
ds2text Export data set to a field delimited text file. Column heading using names or labels can be requested.
sas2xls Export up to fifty SAS datasets and/or SOURCE catalog entries as worksheets of an Excel file.
This macro can write to _WEBOUT to deliver real Excel worksheets from SAS/Intrnet applications.
Requires Perl and module Spreadsheet::WriteExcel.
Thanks to John McNamara @ for answering my questions, his great module makes writing a binary Excel file easy.
A Roger DeAngelis post to SAS-L got me started on this.
Macro programming
add2optn Add a path or other additional information to a SAS option This extension of Peter Craword's start-up quick tip (found at SAS website) will handle repeated invocations and not add information already present.
anyobs Check if a data set has any rows.  
clearmv Set all global macro variables to null Two versions. For v8.2+ consider using %symdel mvar
_delmac_ Delete a macro. Useful when developing autocall macros. Must be invoked on a PROC boundary.
existmac Determines if a macro exists. Must be invoked on a proc boundary.
existmv Determines if a macro variable exists.  
hasvar Determine if a data set has a variable of a given type.  
lastword Returns the last word from a delimited list of words.
Version 8: use %scan (x y z, -1)
Useful for some generic forms of DATA Step BY processing with first. and last. logic.
makelist Create a delimited list of items from a space delimited list of N items. Also creates N macro variables with appropriate numeric suffix. (Aka macro array)
quikmail Fire off an email with a small message: to, subject and body UNIX
reverse Reverse a macro variable's value. For versions before v6.12. Otherwise use
sasname Determine if a string is a valid SAS name. Based on David L. Ward's %SASNAME posted to SAS-L April 11, 2002. Rewritten using verify().
tranwrdi Case insensitive version of tranwrd().  
seplist Replace delimiter in a list of delimited items. Useful when needing a comma separated list of variables for SQL group by, when given space separated list of variables for PROC and DATA by processing. Detailed explanation.
Data set metadata
Most interesting information about a data set can be determined using the attrn() and attrc() functions.
Most interesting information about a data set's structure (it's columns and their metadata) can be determined using the varattr() functions. attr can be fmt, infmt, label, len, name, num, type
See the Online Help or Online Doc for more information about available attributes and functions.
Data management
delabel Remove labels from all columns in a data set.  
deleteds Delete a data set. Must be invoked on a proc boundary.
delete Another data set deleter.  
ds_wait Wait for a data set to become available for exclusive lock. Useful when rebuilding or appending with merge. Uses UNIX sleep.
huntstop Hunt down and stop users with a lock on the data set you must modify. SAS/Share and UNIX.
nextobjn Find the next 2 level name in a sequence with numerically advancing suffixes.  
orderidx Create an index named 'order' for a data set.  
purtysql Pretty print SQL source. Not to shabby. Use with viewsource.ora Sufficient and free. Sample Use.
Better yet, use Revealnet's PL/Formatter with ToadSoft.
viewsource.ora Oracle procedure to extract view source. SAS/Access to Oracle needed. Sample Use.
reorder Writes a SAS program to regenerate a data set. Opens the program into the SAS Editor for easy rearranging of columns, attribute changes and data transformations. (rewritten 11/12/00)
resize Rebuild a data set, shortening all the character columns to minimum space required.  
samelabl Label variables in second data set the same as in the first data set. Generates a PROC step.
SameLabel Label variables in second data set the same as in the first data set. Pure macro, but does not work ... yet.
When MODVAR() function is available to Base SAS it will.
Data processing
compcnt Compare number of rows in by-groups of two data sets. Useful for comparing different extraction techniques that should result in same subset.
FirstCap Given a string of words in all uppercase, convert so only first letter of each word is capitalized DATA Step only
hierarch For a given data set and by group hierarchy, create a data set enumerating parent/child relationships of the by groups. Useful for creating data for SAS/AF Organizational Chart class.
Data presentation
jsTitleAndFootnote ODS HTML Javascripted titles and footnotes of unlimited length.
Sample SAS program, HTML output, Javascript generated
Developed to work around SN-005118 , long TITLE and FOOTNOTE statements receive an unwanted <BR> at or near position 108 when using ODS HTML
webframe Generate an HTML GRSEG previewer with Thumbnails. Modification of SI sample, similar to WEBFRAME device (V7) Suitable for dynamic use since all pieces are named with a timestamp to prevent problematic caching. Sample.
getzips Return list of zipcodes within X miles of another. The $50 dollar macro !
para_sym Generate symbol statements for a parallel plot. Sample use to follow someday.
Roland Rashleigh-Berry's macros - Good general purpose macros and specialized macros for tabulation of safety data in pharmaceutical clinical reporting. Most recent version Archived (06SEP2003)

These macros have not been peer tested for quality assurance and are presented as-is. Some macros may rely on host specific features. Use at your own risk. Send questions or feedback to Richard A. DeVenezia

This page was last updated 29 September 2003.