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|
|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.
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||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 @ cpan.org 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.
|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
|_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.
|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
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.
|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.|
|Developed to work around
, 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. DeVeneziaThis page was last updated 29 September 2003.