CompuCraft

Vision of empowerment - Acrobat Reader Vision

Roger DeAngelis Picture Roger

• SAS Passthru to EXCEL PassEXCEL ^19MAY08

Pharmasug Paper : Program Templates for Report Generation

• Program Templates for Report Generation Paper ^14MAY05
• Code used in Pharmasug Paper Code ^14MAY05

Soap Box Series

• More on SAS Directories-Table Names-Variable Names SOAP0018 ^01AUG03
• My SAS IDE SOAP0017 ^03APR03
• XMLMap Enhancement to V8.2 SAS SOAP0016 ^10JUL01
• Benchmark Reading SAS table vs Flatfile on MVS SOAP015 ^03JUL01
• SAS tables to/from Windows applications using SQL-Server SOAP014 ^03JUL01
• SAS tables to/from Windows applications using XML SOAP013 ^25JUN01
• Some comments SAS with Visual Basic SOAP0009 ^30APR1999
• Matching name and address lists using SAS & Mailers+4 SOAP0003 ^26NOV1998
• Function keys and V8 GUI SOAP0012 ^18DEC1999
• Why the macro language is useful SOAP0011 ^18DEC1999
• Good set of software tools SOAP0010 ^30APR1999
• Some comments on data warehouse design pre Version 7 SAS SOAP0008 ^06JAN1999
• Continuation of SOAP0001 long table and variable names -- macro and non macro SOAP0007 ^22DEC1998
• Advanced subroutine and function calls within a SAS datastep SOAP0006 ^22DEC1998
• Simple subroutine and function calls within a SAS datastep SOAP0005 ^06DEC1998
• Creating source code from metadata tables SOAP0004 ^04DEC1998
• Why I wish I could use long format (memory resident lookups) names in V7 SAS? SOAP0002 ^24NOV1998
• Why should I use long variable names in V7 SAS? SOAP0001 ^24NOV1998
• Why the macro language is useful SOAP0011 ^18DEC1999

New

• Special Characters on a rotated Y axis using cgm and Microsoft Word UTLROTDEP ^15JUN04
• Template Frames for RTF Tables in Microsoft Word UTLFRAMES ^15JUN04
• Zip File of 40 Template Frames for RTF Tables in Microsoft Word UTLFRAMES ^15JUN04
• Programming Trap associated with the SAS merge UTLMRGFIX ^15JUN04
• Interscection of Key Variables across multiple Tables by Ian Whitlock UTLNTRSCT ^15JUN04
• Listing Duplicate Observations UTLLSTDUP ^15JUN04
• Using SAS IML for Clinical Programming UTLIMLLAB ^15JUN04
• Comparison of IML SPlus and SAS Stat UTLIMLSPLSAS ^15JUN04
• Proc Freq but with all the details UTLFRQDET ^15JUN04
• Greek Letters and Symbols for Rtf and MS-Word UTLSYMRTF ^15JUN04
• Inputs and outputs from UTLSYMRTF UTLSYMRTF ^15JUN04
• Minimum Code for a very complex Summary report UTLTBLTEMLAT ^27JUL03
• Output from Minimum Code for a very complex Summary report UTLTBLTEMLATPDF ^27JUL03
• Examples Using Subqueries in SQL UTLSELSQL ^08JUL03
• Saving the Contents of your Windows Program Editor window into a Unix Directory UTLPGMUNIX ^08JUL03
• Tabulate Compared to Proc Report for Higher Dimensional Tables UTLTABRPT ^08JUL03
• Rtf Output Proc Reportfor Higher Dimensional Tables UTLTABRPTRTF ^08JUL03
• Demographic Table in less Lines of code than Standardized Macros UTLDEMSHO ^08JUL03
• Rtf Output Demographic Table UTLDEMSHORTF ^08JUL03
• Line up the decimal point in proc report UTLLYNUP ^08JUL03
• Line up the decimal point Output rtf file UTLLYNUPRTF ^08JUL03
• Error Note and Warning Keywords in the SAS log UTLERRWRD ^05JUL03
• Summary of Errors Warnings Inputs/Outputs and Problem Notes in your SAS log UTLLOGRPT ^05JUL03
• Column, Row and Sub Matrix Totals for a Datastep Two Dimensional Array - More Flexible Sum of Construct UTLSUMOF ^02JUN03
• Creating a Datastep Two Dimensional Array Statement From Proc Freq Counts UTLMKEARY ^02JUN03
• Listing the Subjects in each cell of a 2x2 contingency Table UTLFRQPAT ^01JUN03
• Kartsten Self Validation & Verification macro updated for Version 8.2 VV ^01JUN03

EXCEL/WORD

• Creating a binary MS-EXCEL Spreadsheet using SAS, PERL and named pipes without MS-EXCEL UTLPRLXL ^06APR02
• ODS Style and Proc Report to create MS-Excel/Word Objects UTLODSMS ^10DEC01
• Mutilple EXCEL Sheets to SAS using SQL-Server UTLMXCL ^03JUL01
• Indentifying Outliers using Grubbs Test UTLGRUB ^14JUN01
• SAS to EXCEL using DDE UTLTXLS ^23JUN98
• SAS Dataset from EXCEL Table PERL ODBC UTLOBLD1

• List SAS Table on PC without SAS Installed using VB6 and ODBC UTLVBAS ^01MAY99
• VB6 Executable created by UTLVBAS UTLVBAS ^01MAY99

• List of all tables in ODBC Compliant Win95/98/NT database UTLOBTB ^26APR99
• ODBC Compliant Win95/98/NT database to SAS Tables UTLODBS ^26APR99
• SAS Tables to ODBC Compliant Win95/98/NT database UTLSODB ^26APR99
• Sample EXCEL with marked tables input for UTLODBS UTLODBS0 ^26APR99

• SAS memory resident lookups(formats) functions ZIP->County#/City name/County Name UTLZMLK ^27APR99
• Given Zipcode add State/County/Lat/Long data UTLZMAP ^27APR99
• Placing Zipcode level points on State and/or County Maps UTLZMPA ^27APR99
• Map produced by UTLZMPA Adobe pdf UTLZMPA1 ^27APR99
• Summarizing Zipcode data on US, State and County Maps UTLZMPB ^14MAY99
• Map produced by UTLZMPB - NH with county data UTLZMPB1 ^14MAY99
• Creating High Quality Viewable Encapsulated Postscript Graphics in Microsoft Word - Requires Microsoft Publisher UTLPUBH ^30MAR03

• Which of 100K new customers are in the 100 Million database Hashing UTLDUPG ^03JUL01
• Correcting Phone Numbers for Areacode Splits - requires $400 Subscription for Data UTLNPA ^27MAR01
• Improved Chi Square Analysis for Target Marketing UTLNUCH ^30OCT00
• Create Macro Mania script to load Inexpensive Electronic Organizers UTLLORG ^12AUG2000
• Gains Chart for Continuous Target marketing models UTLTGAN ^8AUG2000
• Create Recoding SAS Code for Target Marketing Models UTLTCDE ^8AUG2000
• Diagnostic Regression Analysis for Continuous Target marketing Model UTLTMDL ^8AUG2000
• Ranking Potential Predictors for Target Marketing UTLTRNK ^8AUG2000
• Software Tools and Steps for Target Marketing UTLTSTP ^8AUG2000
• Estimating Monthly Response Rates for Media Promotions UTLERTE ^8AUG2000
• Transform Continuous Response Variable for Analysis using Optimal Regrouping Algorithms UTLOFIX ^14JUL2000
• Creating Frequencies from UTLOFIX output for use with Regrouping Algorithms UTLOWFQ ^14JUL2000
• Parse AOL Favorite Places into Clear Text File UTLAOLF ^14JUN2000
• Signature Names into Prefix/First/Middle/LastName/Suffix - SAS and External Executable UTLNAMP ^14JUN2000
• Estimating Response Quantities from Multiple Promotions UTLESTP ^14JUN2000
• Generating Response Probabilities for Marketing Promotions UTLCURV ^11JUN2000
• Optimum ReGrouping of Ordinal Frequency data for Marketing Apps UTLOPOR ^11APR2000
• Optimum ReGrouping of Nominal Frequency data for Marketing Apps UTLOPTI ^3APR2000
• Copy all your SAS catalog Source entries to Windows Folder UTLCATF ^03APR2000
• Never Type a Libname Libref with a long path UTLALIB ^13FEB2000
• Create 50 State Tables from One US Table SQL Solution UTLXTRK ^17JAN2000
• Function keys and V8 GUI SOAP0012 ^18DEC1999
• Package for customizing SAS V8 GUI and desktop UTLDMKY ^18DEC99
• Configuration Driven SAS Batch Processing Using PERL UTLPDMO ^18DEC99
• Proper Case Last Names Simple Example UTLPCSE ^28NOV99
• Proper Case Last Names Complete Example need UTLPCSE Lookup Table UTLPCSN ^05NOV99
• Uppercase FirstName to Gender SAS Memory Resident Lookup ie ROY->M UTLFNGN ^17JUN2000
• Uppercase LastName to Ethnic Group SAS Memory Resident Lookup see Label UTLLNET ^17JUN2000
• Matching name and address lists using SAS & Mailers+4 SOAP0003 ^26NOV1998
• Variable selection for Target Marketing Models UTLFSEG ^04DEC98
• Proper Case Last Names UTLPCSE ^28NOV99

• Using TSO services in interactive and batch SAS UTLBTSO ^03JAN99
• Configuration Driven SAS Batch Processing Using PERL UTLPDMO ^04OCT99
• Check IBM tape label SAS 611 Unix UTLVLCK
• Config file driven SAS macros UTLSYSI
• Parser for table driven macros UTLPRSE
• Unix shell script config driven macros UTLSYSC
• Xeq system of macros with pmenu UTLMCRO

Database

• Splitting Big File into Multivolume - Multifile Tape Files OS/390 UTLSTATE ^21MAR01
• Reading delimited text files twice - once for names and lengths - then create table UTLDFLT ^14DEC00
• Minimizing the elapsed time for your MVS Batch Jobs UTLXEQJ ^14DEC00
• How to Update/Insert/Delete Rows and Columns in Excel using ADO/ODBC and Visual Basic UTLUPXL ^26JUL01
• Fast Processing of Flatfiles UTLFASTF ^08DEC01
• Proc Means and Proc Univariate Combined from SAS-L UTLMEAN ^20Jan1998
• Check tables before you merge or join columns UTLMRCK ^17DEC1998
• Proc univariate for character variables beta code UTLMMCH ^25NOV1998
• Distinct levels for all columns in SAS table (cardinality) UTLCRDN ^28NOV1998
• More flexibility in proc summary rollups UTLSUMP ^19NOV98
• Create table and print all duplicate rows UTLFDUP ^16NOV98
• Packing 15 pos decimal digits into one 8 byte float variable UTLPKDC ^15NOV98
• Unpacking 15 decimal digits from float var into several decimal char vars UTLPKUD ^15NOV98
• ASCII DBASEIII file created by MS-EXCEL - MS EXCEL to MVS SAS Table without PC-SAS UTLDBSA ^20DEC98
• ASCII DBASEIII file created on MVS - MVS SAS Table to MS EXCEL without PC-SAS UTLS2DB ^17OCT98
• Draw a random sample of size M from N observations UTLRSEL ^08MAY1998
• Does a column value exist using where clause in macro UTLMACX ^08MAY1998
• File attributes into macro variables MVS/VM/VSE/OS2 etc UTLGDCI ^27MAR1998
• MVS file size and number of records w/o reading file UTLDSDB ^16APR1998
• MVS file attributes into macro vars improved UTLDSCB ^02MAY1998
• Does the File, Catalog, View, Dataset or Access descriptor exist? UTLOXST ^20Jan1998
• Does the dataset contain an arbitrary value using macro language? UTLCDAT ^13FEB1998
• Access remote SAS xport dataset as though local -improved UTLFTPX ^25Oct1997
• Apply transactions to RDMS or SAS table UTLDMOD
• Capitalize all character variables UTLCAPS
• Column values in one dataset and not the other UTLXCPT
• Copy a file from IBM labeled tape UTLDITO
• Create a delta dataset for RDBMS table update UTLDLTA
• Create dataset from csv file UTLNFLT
• DBASEIII to SAS Dataset UTLDB2S
• Data dictionary for a list of datasets UTLDICM
• Does Oracle/Sybase table exist? UTLSPEX
• Does Sybase table exist without using SAS-Access? UTLSPCO
• Duplicate sort keys in a SAS dataset UTLCNDP
• EXCEL table from SAS Dataset PERL ODBC UTLOBAC1
• Information on UTLOB macros UTLOB
• MS-Access Table from SAS Dataset PERL ODBC UTLOBAC
• Print a FORMAT Catalog Entry UTLFPRT
• Remove control characters ie hex 0D from all char variables UTLCLEN
• SAS Dataset to DBASEIII UTLS2DB
• SAS FTP UTLFTP
• SAS ODBC without the rest of SAS UTLODBC
• SAS dataset from MS-Access Table PERL ODBC UTLOBLD
• Simple SAS program to create entity diagrams - working drafts UTLSDSN ^18Dec1997
• Vertical print of random ob UTLXPRT

Menus

• Netscape as front end to SAS - proof of concept UTLJAVA
• Using just Base SAS build FRAME like macro windows UTLBUTN
• X (enter) on right mouse button navigate %windows w/o keyboard UTLBUFX

• Conceptual Data Model for SAS/Oracle MDDB Map Hierarchy Ajax Company UTLMDDB ^01Nov1997
• SAS code for star schema of Map Hierarchy in Oracle Express MDDB UTL1FMO ^01Nov1997

Packages

• SAS consultants COBOL to SAS Tools UTLCOBL ^12Aug1997
• SAS consultants Toolkit A UTLTULA ^12Aug1997
• SAS consultants Toolkit B UTLTULB ^12Aug1997

Reporting

• Making headers and footers part of an rtf Table UTLHDFT ^30MAR03
• ODS Style and Proc Report to create MS-Excel/Word Objects UTLODSMS ^10DEC01
• Printing one observation per page with user defined template UTLPCRF ^23Oct1997
• Banner page for printer output UTLXPLD
• Clinical Trial Demographics Table Program UTLDEMO
• Collapse SAS dataset to one row with max char and numeric values IML UTLMVEC
• Example of title statement style macro using parmbuff UTLJSTY
• Executable to convert SAS Output to Adobe PDF Oakworth UTLGYMN ^27Jul1997
• Fix BOX option problem in Proc Report UTLFXCT ^04Jan1998
• Print batch SAS print files from DOS window UTLCSET
• Printing one observation per page with user defined template UTLPCRF ^23Oct1997
• Right center and left justification in titles and footnotes UTLJUST
• Standard title UTLTYTL
• Tabulate->Acrobat PDF w Base SAS/UTLGYMN/Acrobat Reader(free) UTLSTOP ^27Jul1997

System

• VENN Diagram for Three Tables UTLVEN3 ^27MAY03
• Eliminating DIvision by Zero messages from the SAS log UTLDIVCK ^30MAR03
• Macro subroutines that create new columns and evaluate expressions based on data values UTLPUTZ ^07JUL02
• A more functional alternative to call symput - using the resolve function UTLPUT ^06APR02
• Macro subroutines that operate with the contents of datastep variables during datastep execution UTLGETW ^06APR02
• SAS system setup for MVS ESA UTLDBAT ^07JUN98
• Proc Means and Proc Univariate Combined from SAS-L UTLMEAN ^20Jan1998
• Executing Procs inside Logical Data steps with Call Set & Fetchobs UTLDSQL ^21Jan1998
• Acces remote SAS xport dataset as though local -improved UTLFTPX ^25Oct1997
• Autoexec and function key settings for Win95 SAS612 UTLDMKY
• Automatic documentation for your macro tools UTLSUPS
• Bring up TSO batch execute SAS and SAS objects UTLTSO ^15Nov1997
• Change the order of variables in SAS dataset UTLORDR
• Check IBM tape label SAS 611 Unix UTLVLCK
• Clean out the SAS work library catalogs-datasets-views-macros UTLCLIP ^17Aug1997
• Config file driven SAS macros UTLSYSI
• Copy a SAS table to different name in same database UTLCOPY ^10Nov1997
• Copy a file from IBM labeled tape UTLDITO
• Create char to char formats UTLFMAT
• Create dataset from csv file UTLNFLT
• Create datasets based on a variables values UTLCUTR
• Cross platform delete of external file UTLFKIL
• Dataset variable to macro variable using SCLfunctions Base SAS UTLMTOS
• Datastep functions in macro language from SAS UTLMISC
• Debuging options UTLOPTS
• Execute macros stored in SAS datasets using Base SAS SCL code UTLSTMA ^20Aug1997
• Expand Win95 DMS editor to 255 characters UTLXPNE ^18Sep1997
• Hex dump of file like list command UTLRULR
• How to build command style macros UTLCON
• How to emulate cards functionality inside a macro UTLFBIZ
• Logging the state of batch execution UTLSTRT/UTLRCDE/UTLEND UTLPLTT ^10Aug1997
• MS-Access Table names into macro variables using SAS & PERL ODBC UTLOB
• Macro cross ref listing UTLXREF
• Number of rows in SAS table UTLNOBZ
• Print batch SAS print files from DOS window UTLCSET
• Production options UTLNOPTS
• Put your autocall library first in search order UTLAUTO
• Reading & writing remote SAS data UTLFTPR
• Remove all formats and informats UTLRFMT
• Restore calling macros librefs use with utllbcl UTLLBAL
• Restore macro variables from SAS dataset SCLfunctions Base SAS UTLMKMC
• Returning macro vars to calling programs scope UTLMVOT ^08Jan1998
• SAS FTP UTLFTP
• Save and clear calling macros librefs UTLLBCL
• Storing functions in formats UTLFMTX ^18Sep1997
• Storing macro arguments in SAS datasets UTLMACD
• Unix shell script config driven macros UTLSYSC
• Venn diagram for two tables UTLVENN
• Macro subroutines that operate with the contents of datastep variables during datastep execution ^06APR02
• Vertical print of random ob UTLXPRT
• X (enter) on right mouse button navigate %windows w/o keyboard UTLBUFX

Text

• Convert character variables to numeric floating point with same name UTLCTON ^13AUG98
• Capitalize all character variables UTLCAPS
• Converting EBCDIC files to ASCII UTLEB2A
• Example of title statement style macro using parmbuff UTLJSTY
• Executable to convert SAS Output to Adobe PDF Oakworth UTLGYMN ^27Jul1997
• Extract all numeric characters from a string RXCHANGE UTLRXNU
• Generate list of zero padded integers in editor FPUT FWRITE UTLIOTA
• Minimum digit in numeric string using RXSUBSTR UTLRXMX
• Number of occurrences of a character within a string RXCHANGE UTLRXOC
• Number of words in a macro list- improved UTLWRDS ^08Nov1997
• Reading Writing Manipulating Long(32K) character variables UTLSPRO
• Right center and left justification in titles and footnotes UTLJUST
• Standard title UTLTYTL
• Sum of digits in a numeric string RXCHANGE UTLRXSM
• Tabulate->Acrobat PDF w Base SAS/UTLGYMN/Acrobat Reader(free) UTLSTOP ^27Jul1997
• Uppercase the first character in each word of a macro string UTLUPCS ^07Sep1997
• Using SAS Graph to create technical pages in Adobe postscript UTLPBSH ^09Jan1998
• Using special characters in SAS Monospace Font UTLFONT
• Words in a string into a sequence of macro variables UTLMARY ^08Nov1997

• Summary File 1 is comprised of the 100% complete count , and is the most useful, detailed and comprehensive data that the Census will release. All other data is less granular. The data that will be released late next year (SF3) will be based on the long form and will contain data such as education, income and occupation. Although these data represent useful items the granularity may be too coarse for some marketing purposes.

• CompuCraft has added Zip+4 and SAS matching software so that users can concatenate detailed Census 2000 data items to internal lists. CompuCraft has created 40 tables from the 2,120 Census state comma delimited files. Each of the 40 tables contains the 50 states, DC and Puerto Rico. The SF1 data consists of 8,432 columns in 40 tables ( use the magnifier in Acrobat reader to see the tables and relationships). The 8,432 fields have been split into 40 tables forming a star schema. The central geography table has 9,541,315 records and 83 columns with 8,262,363 at the block level and 152,337 at the tract level. Twenty-five satellite tables have 724,015 records each and a total of 5,203 fields, the finest geography on these tables is the tract level. The remaining 14 satellite tables have 9,541,315 records each (8,262,363 at the block level) and a total 3,146 fields. An multi page conceptual model diagram with cutout guidelines provides another view of the data model. The 25 tables, Segments 12 through 36 sometimes called the PCT tables, contain rather detailed data such as sex by age with over 100 age categories, 15 of the 25 tables contain the same type of data, just broken out by race or racial groups. Tables 1 through 11, sometimes called the P tables, contain block level ethnic data by age and houshold type. Tables 37 through 39, sometimes call the H tables contain more detaild data on Household composition such as non traditional households, institutions and group quarters. A data model report with text describing all entities and relationships provides programmers with the details they need to implement the data model in any DBMS, including SAS.
• Census 2000 County Demographics with Fips and County Code in SAS Table UTLCNTY ^25MAY01

• All the data and descriptions presented here relate directly to the exhaustive Census documentation . CompuCraft has used the Census structure and naming conventions throughout. CompuCraft has created a report which attempts to summarize the data contained in the 625 page Census documentation. Detailed information on the geographic header columns allow users to select from more than 20 geographies. Detailed data on the 39 satellite tables ; such as column name, column denominators and text descriptions allow programmers to not only normalize the data, but select columns appropriate for thier needs.
• Census 2000 SF1 Record Counts for Geo Header and 39 Segments 2080 Files UTL2KRC ^13OCT01
• Census 2000 County Demographics with Fips and County Code in SAS Table UTLCNTY ^25MAY01
• Census 2000 SF1 Metadata UTL2KMT ^30AUG01
• Census 2000 Zip Level Ethnic table based on redistricting files with matching SAS Software UTLC2KZ ^17NOV01
• Census 2000 Complete SF1 Diagram showing all Data Items 50 pages UTLDATF ^19NOV01