Technology Tales

%DATATYP Autocall SAS Macro

The %DATATYP autocall macro in SAS is used to determine whether a given value is numeric or character in nature. When the argument consists of digits, a leading plus or minus sign, a decimal point or a scientific or floating-point exponent, the macro returns the value NUMERIC; otherwise, it returns CHAR. It does not recognise hexadecimal numbers.

The macro requires the MAUTOSOURCE system option to function and is drawn from a library supplied by SAS, which may not be available at all sites or may exist in a site-specific version, in which case on-site SAS support personnel should be consulted. A practical use of the macro is to validate inputs before performing arithmetic operations, as demonstrated by an example in which an addition macro checks that both of its arguments are numeric before attempting a calculation, logging an error message if either argument fails that check.

Leaflet

An open-source JavaScript library designed for creating mobile-friendly interactive maps, Leaflet offers a lightweight solution with minimal file size and a focus on simplicity, performance and usability. It supports a wide range of mapping features including tile layers, markers, vector shapes and image overlays, along with interactive elements such as drag panning, zooming and keyboard navigation. The library provides smooth animations, customisable visual components and compatibility with various map projections, while its modular structure allows for efficient feature inclusion. Leaflet is supported across major desktop and mobile browsers and its development is driven by a community of contributors, with widespread adoption by organisations in both public and private sectors.

Formatting Numeric Print Output in Java

Java's printf and format methods, part of the PrintStream class in the java.io package, offer developers far greater control over numeric output than the basic print and println methods. Both methods function identically and accept a format string alongside a variable number of arguments, using special format specifiers that begin with a percent sign and end with a converter character to define how each value should be displayed. These specifiers can handle decimal integers, floating-point numbers, newline characters and date or time values, with optional flags allowing for controls such as width, decimal precision, left justification, leading zeroes and locale-specific formatting. For scenarios requiring even finer control, the DecimalFormat class provides a pattern-based approach to managing grouping separators, decimal separators, prefixes, suffixes and leading or trailing zeroes, though at the cost of added code complexity.

Excel VBA – Read Data from a Closed Excel File or Workbook without Opening it

A practical method exists in VBA for automatically pulling data from a closed Excel file into a destination workbook without manually opening the source file. By writing a procedure within the Workbook\_Open() event of the destination file, the process triggers automatically each time that file is opened. The approach involves briefly opening the source workbook in read-only mode behind the scenes, counting its rows, iterating through the data and copying it across to the destination file, before closing the source again without saving.

Setting the Application.ScreenUpdating property to false during this process helps improve speed and prevents any visual disruption on screen. The result is a reliable, largely automated solution that reduces manual effort, minimises errors and ensures that anyone opening the destination file will always see the most current figures drawn from the source.

How to create stunning visualisations from scratch using Python

Visualisation is a core skill for data scientists, as it helps communicate analytical insights clearly and allows the human brain to identify patterns and trends far more readily than through other means. Using Python's Matplotlib and Seaborn libraries, it is possible to build a wide range of charts, from basic trend lines and scatter plots to bar charts, pie charts, donut charts and heatmaps.

Matplotlib offers extensive customisation and underpins many other Python visualisation libraries, though it requires more lines of code to produce polished results. Seaborn, which is built on top of Matplotlib, addresses this by enabling attractive, ready-to-use charts with minimal coding, making it particularly well suited to exploratory data analysis. One of its most useful features is the pair plot, which compares every attribute in a dataset against every other attribute in a single line of code.

Beyond static images, Python also supports the creation of interactive charts that can be exported as HTML files and opened in a browser, giving audiences the ability to zoom and explore data in greater detail. Data transformation using the Pandas library is an important preparatory step throughout, ensuring datasets are structured appropriately before any chart is produced.

Office VBA Reference

Visual Basic for Applications is a programming language designed to extend Office applications by enabling automation of repetitive tasks and the creation of custom functionalities. It allows users to perform actions typically done manually through the interface, such as formatting documents or interacting with users, and can be used to enhance Office applications with tailored features that meet specific business needs. This resource is aimed at experienced users seeking to leverage VBA for improving efficiency and customising Office tools through code.

SAS Usage Note 49421: A "Cannot write image" error might occur with ODS Graphics and the SAS/GRAPH® Statistical Graphics (SG) procedures

A "Cannot write image" error may occur in SAS when generating graphics using ODS Graphics or SG procedures, typically due to insufficient disk permissions, unauthorised access to a directory, or conflicts between ODS destinations. This commonly arises when attempting to write graphics to a specific ODS destination while the listing destination remains open, which can be resolved by closing the listing destination with ods listing close; or specifying a valid directory using the GPATH= option in the ods listing statement. Alternatively, wrapping procedures in ods html statements with the PATH= option to define an accessible output directory may also address the issue. The error is relevant across multiple operating systems, including Windows, Linux and various Unix variants, and applies to SAS 9.4M2 and later versions.

SAS Usage Note 39705: Troubleshooting "Sort initialisation failure" and "Sort execution failure" errors in Windows and UNIX environments

Errors encountered during SAS SORT and SQL procedures, such as "Sort initialisation failure" and "Sort execution failure," often stem from insufficient disk space, memory, or system resource limits, incorrect SAS or operating system configurations, or inadequate permissions for temporary directories. Troubleshooting involves reviewing SAS log details using commands like proc options to assess memory and utility settings, verifying user permissions for directories referenced by the WORK and UTILLOC options, checking system-imposed limits with tools like ulimit -a and ensuring sufficient space in temporary storage locations. Adjustments to system parameters, such as increasing MEMSIZE or SORTSIZE based on memory usage statistics, may be necessary. These issues can occur across a wide range of operating systems, requiring careful monitoring of resource allocation and temporary file management to resolve failures effectively.

Announcing LAMBDA: Turn Excel formulas into custom functions LAMBDA function

Microsoft Excel has introduced a new function called LAMBDA, which allows users to create their own reusable custom functions using Excel's native formula language, without the need for VBA, macros or JavaScript. Previously, creating custom functions required writing code in a separate language such as JavaScript, but LAMBDA removes that barrier, making the capability accessible to non-programmers.

A LAMBDA function accepts up to 253 parameters and a calculation as its final argument, and once named via the Name Manager under the Formulas tab, it can be called anywhere within a workbook just like any built-in Excel function. This brings significant practical benefits, such as reducing repetitive copy-and-pasting of complex formulas, making spreadsheets easier to read and maintain, and allowing errors to be corrected in a single place rather than across multiple cells.

Notably, LAMBDA also supports recursion, meaning a function can call itself, which enables looping behaviour that was previously only achievable through scripting. The function is also compatible with dynamic arrays and rich data types, expanding the range of calculations that can be performed, such as computing distances between cities using geographic coordinates. If an incorrect number of arguments is passed or the function is entered in a cell without being called, Excel will return an error, so following standard formula best practices is advisable.

How to Use Parameters in PowerShell Part I

How to Use Parameters in PowerShell Part II

PowerShell offers two approaches to passing values into scripts: unnamed arguments via the $args array, and named parameters defined using a param() block. Named parameters are generally preferred as they support type enforcement, default values, mandatory fields and validation sets that restrict inputs to predefined options.

Boolean and switch datatypes allow flag-style parameters, with switch being simpler as it requires no assigned value. Parameters can also accept arrays, enabling a single named parameter to handle multiple values at once. For pipeline-based workflows, the ValueFromPipeline attribute allows values to be passed via the pipe operator, with begin, process and end blocks controlling how each piped value is handled.

Where multiple properties need to be piped simultaneously, ValueFromPipelineByPropertyName allows a script to receive named properties from the output of other cmdlets. It is also possible to combine pipelined and directly passed parameters within the same script, provided parameter ordering is managed carefully to avoid unintended value assignment. Together, these techniques make PowerShell scripts considerably more flexible, portable and maintainable across different environments.