CompuCraft

Vision of empowerment - Acrobat Reader Vision

Roger DeAngelis Picture Roger

Recent Odds and Ends

• All the Catalogs, SASAUTOS Macros and .SAS Programs in SAS 9.1.3 dotsas.txt ^04JUL09
• Meta Analysis Forest Plot Package forestpptdoc ^04JUL09
• Meta Analysis Forest Plot Powerpoint Output forestppt ^04JUL09
• Meta Analysis Forest Plot Microsoft Word Output forestdoc ^04JUL08
• Directory Tools Unix and Windows DirectoryTools.txt ^28JUN09
• A Better Macro Interface BetterMacro ^28JUN09
• SAS Passthru to EXCEL PassEXCEL ^19MAY08

Pharmasug Paper : Program Templates for Report Generation

• Program Templates for Report GenerationPaper ^14MAY05
• Code used in Pharmasug PaperCode ^14MAY05

Soap Box Series

• More on SAS Directories-Table Names-Variable NamesSOAP0018 ^01AUG03
• My SAS IDESOAP0017 ^03APR03
• XMLMap Enhancement to V8.2 SASSOAP0016 ^10JUL01
• Benchmark Reading SAS table vs Flatfile on MVSSOAP015 ^03JUL01
• SAS tables to/from Windows applications using SQL-ServerSOAP014 ^03JUL01
• SAS tables to/from Windows applications using XMLSOAP013 ^25JUN01
• Some comments SAS with Visual BasicSOAP0009 ^30APR1999
• Matching name and address lists using SAS & Mailers+4 SOAP0003 ^26NOV1998
• Function keys and V8 GUISOAP0012 ^18DEC1999
• Why the macro language is usefulSOAP0011 ^18DEC1999
• Good set of software toolsSOAP0010 ^30APR1999
• Some comments on data warehouse design pre Version 7 SASSOAP0008 ^06JAN1999
• Continuation of SOAP0001 long table and variable names -- macro and non macroSOAP0007 ^22DEC1998
• Advanced subroutine and function calls within a SAS datastepSOAP0006 ^22DEC1998
• Simple subroutine and function calls within a SAS datastepSOAP0005 ^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 usefulSOAP0011 ^18DEC1999

New

• Special Characters on a rotated Y axis using cgm and Microsoft WordUTLROTDEP ^15JUN04
• Template Frames for RTF Tables in Microsoft WordUTLFRAMES ^15JUN04
• Zip File of 40 Template Frames for RTF Tables in Microsoft WordUTLFRAMES ^15JUN04
• Programming Trap associated with the SAS mergeUTLMRGFIX ^15JUN04
• Interscection of Key Variables across multiple Tables by Ian WhitlockUTLNTRSCT ^15JUN04
• Listing Duplicate ObservationsUTLLSTDUP ^15JUN04
• Using SAS IML for Clinical ProgrammingUTLIMLLAB ^15JUN04
• Comparison of IML SPlus and SAS StatUTLIMLSPLSAS ^15JUN04
• Proc Freq but with all the detailsUTLFRQDET ^15JUN04
• Greek Letters and Symbols for Rtf and MS-WordUTLSYMRTF ^15JUN04
• Inputs and outputs from UTLSYMRTFUTLSYMRTF ^15JUN04
• Minimum Code for a very complex Summary reportUTLTBLTEMLAT ^27JUL03
• Output from Minimum Code for a very complex Summary reportUTLTBLTEMLATPDF ^27JUL03
• Examples Using Subqueries in SQLUTLSELSQL ^08JUL03
• Saving the Contents of your Windows Program Editor window into a Unix DirectoryUTLPGMUNIX ^08JUL03
• Tabulate Compared to Proc Report for Higher Dimensional TablesUTLTABRPT ^08JUL03
• Rtf Output Proc Reportfor Higher Dimensional TablesUTLTABRPTRTF ^08JUL03
• Demographic Table in less Lines of code than Standardized MacrosUTLDEMSHO ^08JUL03
• Rtf Output Demographic TableUTLDEMSHORTF ^08JUL03
• Line up the decimal point in proc reportUTLLYNUP ^08JUL03
• Line up the decimal point Output rtf fileUTLLYNUPRTF ^08JUL03
• Error Note and Warning Keywords in the SAS logUTLERRWRD ^05JUL03
• Summary of Errors Warnings Inputs/Outputs and Problem Notes in your SAS logUTLLOGRPT ^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-EXCELUTLPRLXL ^06APR02
• ODS Style and Proc Report to create MS-Excel/Word ObjectsUTLODSMS ^10DEC01
• Mutilple EXCEL Sheets to SAS using SQL-ServerUTLMXCL ^03JUL01
• Indentifying Outliers using Grubbs TestUTLGRUB ^14JUN01
• SAS to EXCEL using DDE UTLTXLS ^23JUN98
• SAS Dataset from EXCEL Table PERL ODBCUTLOBLD1

• 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 MapsUTLZMPB ^14MAY99
• Map produced by UTLZMPB - NH with county dataUTLZMPB1 ^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 HashingUTLDUPG ^03JUL01
• Correcting Phone Numbers for Areacode Splits - requires $400 Subscription for DataUTLNPA ^27MAR01
• Improved Chi Square Analysis for Target MarketingUTLNUCH ^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 FileUTLAOLF ^14JUN2000
• Signature Names into Prefix/First/Middle/LastName/Suffix - SAS and External ExecutableUTLNAMP ^14JUN2000
• Estimating Response Quantities from Multiple PromotionsUTLESTP ^14JUN2000
• Generating Response Probabilities for Marketing PromotionsUTLCURV ^11JUN2000
• Optimum ReGrouping of Ordinal Frequency data for Marketing AppsUTLOPOR ^11APR2000
• Optimum ReGrouping of Nominal Frequency data for Marketing AppsUTLOPTI ^3APR2000
• Copy all your SAS catalog Source entries to Windows FolderUTLCATF ^03APR2000
• Never Type a Libname Libref with a long pathUTLALIB ^13FEB2000
• Create 50 State Tables from One US Table SQL SolutionUTLXTRK ^17JAN2000
• Function keys and V8 GUISOAP0012 ^18DEC1999
• Package for customizing SAS V8 GUI and desktopUTLDMKY ^18DEC99
• Configuration Driven SAS Batch Processing Using PERLUTLPDMO ^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 PERLUTLPDMO ^04OCT99
• Check IBM tape label SAS 611 UnixUTLVLCK
• Config file driven SAS macrosUTLSYSI
• Parser for table driven macros UTLPRSE
• Unix shell script config driven macrosUTLSYSC
• Xeq system of macros with pmenu UTLMCRO

Database

• Splitting Big File into Multivolume - Multifile Tape Files OS/390UTLSTATE ^21MAR01
• Reading delimited text files twice - once for names and lengths - then create tableUTLDFLT ^14DEC00
• Minimizing the elapsed time for your MVS Batch JobsUTLXEQJ ^14DEC00
• How to Update/Insert/Delete Rows and Columns in Excel using ADO/ODBC and Visual BasicUTLUPXL ^26JUL01
• Fast Processing of FlatfilesUTLFASTF ^08DEC01
• Proc Means and Proc Univariate Combined from SAS-LUTLMEAN ^20Jan1998
• Check tables before you merge or join columnsUTLMRCK ^17DEC1998
• Proc univariate for character variables beta codeUTLMMCH ^25NOV1998
• Distinct levels for all columns in SAS table (cardinality) UTLCRDN ^28NOV1998
• More flexibility in proc summary rollupsUTLSUMP ^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 macroUTLMACX ^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 -improvedUTLFTPX ^25Oct1997
• Apply transactions to RDMS or SAS tableUTLDMOD
• Capitalize all character variables UTLCAPS
• Column values in one dataset and not the otherUTLXCPT
• Copy a file from IBM labeled tape UTLDITO
• Create a delta dataset for RDBMS table updateUTLDLTA
• Create dataset from csv file UTLNFLT
• DBASEIII to SAS DatasetUTLDB2S
• Data dictionary for a list of datasetsUTLDICM
• Does Oracle/Sybase table exist? UTLSPEX
• Does Sybase table exist without using SAS-Access? UTLSPCO
• Duplicate sort keys in a SAS datasetUTLCNDP
• EXCEL table from SAS Dataset PERL ODBCUTLOBAC1
• Information on UTLOB macrosUTLOB
• MS-Access Table from SAS Dataset PERL ODBCUTLOBAC
• Print a FORMAT Catalog Entry UTLFPRT
• Remove control characters ie hex 0D from all char variablesUTLCLEN
• SAS Dataset to DBASEIII UTLS2DB
• SAS FTP UTLFTP
• SAS ODBC without the rest of SASUTLODBC
• SAS dataset from MS-Access Table PERL ODBCUTLOBLD
• Simple SAS program to create entity diagrams - working draftsUTLSDSN ^18Dec1997
• Vertical print of random ob UTLXPRT

Menus

• Netscape as front end to SAS - proof of conceptUTLJAVA
• Using just Base SAS build FRAME like macro windowsUTLBUTN
• X (enter) on right mouse button navigate %windows w/o keyboardUTLBUFX

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

Packages

• SAS consultants COBOL to SAS ToolsUTLCOBL ^12Aug1997
• SAS consultants Toolkit AUTLTULA ^12Aug1997
• SAS consultants Toolkit BUTLTULB ^12Aug1997

Reporting

• Making headers and footers part of an rtf Table UTLHDFT ^30MAR03
• ODS Style and Proc Report to create MS-Excel/Word ObjectsUTLODSMS ^10DEC01
• Printing one observation per page with user defined templateUTLPCRF ^23Oct1997
• Banner page for printer outputUTLXPLD
• Clinical Trial Demographics Table Program UTLDEMO
• Collapse SAS dataset to one row with max char and numeric values IMLUTLMVEC
• Example of title statement style macro using parmbuffUTLJSTY
• Executable to convert SAS Output to Adobe PDF OakworthUTLGYMN ^27Jul1997
• Fix BOX option problem in Proc ReportUTLFXCT ^04Jan1998
• Print batch SAS print files from DOS windowUTLCSET
• Printing one observation per page with user defined templateUTLPCRF ^23Oct1997
• Right center and left justification in titles and footnotesUTLJUST
• 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 valuesUTLPUTZ ^07JUL02
• A more functional alternative to call symput - using the resolve functionUTLPUT ^06APR02
• Macro subroutines that operate with the contents of datastep variables during datastep executionUTLGETW ^06APR02
• SAS system setup for MVS ESA UTLDBAT ^07JUN98
• Proc Means and Proc Univariate Combined from SAS-LUTLMEAN ^20Jan1998
• Executing Procs inside Logical Data steps with Call Set & Fetchobs UTLDSQL ^21Jan1998
• Acces remote SAS xport dataset as though local -improvedUTLFTPX ^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 objectsUTLTSO ^15Nov1997
• Change the order of variables in SAS datasetUTLORDR
• Check IBM tape label SAS 611 UnixUTLVLCK
• Clean out the SAS work library catalogs-datasets-views-macrosUTLCLIP ^17Aug1997
• Config file driven SAS macrosUTLSYSI
• Copy a SAS table to different name in same databaseUTLCOPY ^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 SASUTLMTOS
• Datastep functions in macro language from SASUTLMISC
• Debuging options UTLOPTS
• Execute macros stored in SAS datasets using Base SAS SCL codeUTLSTMA ^20Aug1997
• Expand Win95 DMS editor to 255 charactersUTLXPNE ^18Sep1997
• Hex dump of file like list command UTLRULR
• How to build command style macrosUTLCON
• How to emulate cards functionality inside a macroUTLFBIZ
• Logging the state of batch execution UTLSTRT/UTLRCDE/UTLENDUTLPLTT ^10Aug1997
• MS-Access Table names into macro variables using SAS & PERL ODBCUTLOB
• Macro cross ref listing UTLXREF
• Number of rows in SAS tableUTLNOBZ
• Print batch SAS print files from DOS windowUTLCSET
• Production options UTLNOPTS
• Put your autocall library first in search orderUTLAUTO
• Reading & writing remote SAS data UTLFTPR
• Remove all formats and informatsUTLRFMT
• Restore calling macros librefs use with utllbclUTLLBAL
• Restore macro variables from SAS dataset SCLfunctions Base SASUTLMKMC
• Returning macro vars to calling programs scopeUTLMVOT ^08Jan1998
• SAS FTP UTLFTP
• Save and clear calling macros librefsUTLLBCL
• Storing functions in formatsUTLFMTX ^18Sep1997
• Storing macro arguments in SAS datasetsUTLMACD
• Unix shell script config driven macrosUTLSYSC
• 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 keyboardUTLBUFX

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 parmbuffUTLJSTY
• Executable to convert SAS Output to Adobe PDF OakworthUTLGYMN ^27Jul1997
• Extract all numeric characters from a string RXCHANGEUTLRXNU
• Generate list of zero padded integers in editor FPUT FWRITEUTLIOTA
• Minimum digit in numeric string using RXSUBSTR UTLRXMX
• Number of occurrences of a character within a string RXCHANGEUTLRXOC
• Number of words in a macro list- improvedUTLWRDS ^08Nov1997
• Reading Writing Manipulating Long(32K) character variablesUTLSPRO
• Right center and left justification in titles and footnotesUTLJUST
• 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 stringUTLUPCS ^07Sep1997
• Using SAS Graph to create technical pages in Adobe postscript UTLPBSH ^09Jan1998
• Using special characters in SAS Monospace FontUTLFONT
• Words in a string into a sequence of macro variablesUTLMARY ^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 TableUTLCNTY ^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 FilesUTL2KRC ^13OCT01
• Census 2000 County Demographics with Fips and County Code in SAS TableUTLCNTY ^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