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 |
FileList
(deprecated) |
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  |
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. |
| |
| 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
%sysfunc(reverse(&<macro-variable>)) |
| 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 any of the SQL Pretty or SQL Formatter websites out there. |
| 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
Usage |
| webframe |
Generate an HTML GRSEG previewer with Thumbnails. Modification a SAS sample named "other-webframe.html", which is similar to the Version 7 WEBFRAME device. |
Suitable for dynamic use since all pieces are named with a
timestamp to prevent problematic caching. Sample. |
| |
| Miscellaneous |
| 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