Query the Windows registry within your SAS program

On the SAS-L mailing list, a participant posed this question (paraphrased):

How can I tell which date format my Windows session is using: European format (with day first) versus USA format (with month first)? I’m reading in output from a Windows file listing, and need to know how to interpret the date value.

There may be more than one way to skin this cat, but one of the responses pointed at a Windows registry key as one cue to discover the “short date format”, which can differ depending upon your regional settings. Following that path, the question then becomes: how can I query the Windows registry key from within my SAS program?

Answer: There is a REG QUERY command that is built into Windows. If you can run that command from within your program, then you can parse the result and get your answer.

Here’s an example to discover the “short date” format on your Windows system:

/* Requires XCMD privileges */
filename reg pipe
  'reg query "HKEY_CURRENT_USER\Control Panel\International" /v sShortDate';
 
data _null_;
  infile reg dsd;
  input;
  if (find(_infile_,'sShortDate')>0) then
    do;
      result = scan(_infile_,-1,' ');
      call symput('SHORTDATE',result);
    end;
run;
 
%put Short date format is &shortdate.;

Result (with my United States regional settings):

Short date format is M/d/yyyy

The discovery of the REG QUERY command got some of the SAS-L participants kind of excited (you know, it doesn’t take much…), and they began to wonder what other goodies could be gleaned from the Windows registry. One key of interest is the infamous TypeGuessRows key, which affects the behavior of PROC IMPORT with Microsoft Excel as it determines column types.

There are some tricky parts to deciphering this key. First, the relevant key was in one location in SAS 9.1 and earlier (which uses Microsoft Jet database engine), and in a different location in SAS 9.2m2 and later (using Microsoft ACE).

The second complication is that on 64-bit systems, the Windows registry is segmented into a 32-bit registry and a 64-bit registry. (That’s yet another “gotcha” of 64-bit applications on Windows.) You must query the correct key for the process that will read the Excel file. If you’re using 64-bit SAS to read Excel files using DBMS=EXCEL or the EXCEL library engine, query the 64-bit key. If you’re using 64-bit SAS to read Excel files using DBMS=EXCELCS or the PCFILES library engine (thus using the 32-bit PC Files Server), then query the 32-bit key.

From a 64-bit process, you can query the 32-bit registry. The 32-bit key “address” will have a Wow6432Node level in the middle of it.

Here’s an example of querying the 32-bit key from a 32-bit SAS session (good for SAS 9.1 and earlier):

%let key = HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel;
/* Requires XCMD privileges */
filename reg pipe
  "reg query ""&key"" /v TypeGuessRows";
 
data _null_;
  infile reg dsd;
  length result 8;
  format result 4.;
  input;
  if (find(_infile_,'TypeGuessRows')>0) then
    do;
      /* Value stored as hexadecimal */
      result = input(trim(scan(_infile_,-1,'x')),hex2.);
      call symput('TypeGuessRows_JET',result);
    end;
run;
 
%put Excel (Jet) TypeGuessRows is %trim(&TypeGuessRows_JET.);

Typical result:

Excel (Jet) TypeGuessRows is 8

For those running the 64-bit version of SAS 9.2M2 or later (including SAS 9.3), this example is probably more relevant:

%let key = HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel;
/* Requires XCMD privileges */
filename reg pipe
  "reg query ""&key"" /v TypeGuessRows";
 
data _null_;
  infile reg dsd;
  length result 8;
  format result 4.;
  input;
  if (find(_infile_,'TypeGuessRows')>0) then
    do;
      result = input(trim(scan(_infile_,-1,'x')),hex2.);
      call symput('TypeGuessRows_ACE',result);
    end;
run;
 
%put Excel (ACE) TypeGuessRows is %trim(&TypeGuessRows_ACE.);

And here’s the typical result:

Excel (ACE) TypeGuessRows is 8

The key value in the code is very long and might be difficult to see in this code sample, so I’ll repeat it here in bold: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel.

Querying the registry is fun, interesting (if you like that sort of thing), and mostly harmless. Modifying the registry is a little bit risky. There is a REG ADD command that allows you to modify existing registry entries if you have the privileges to do so. That’s I’ll say about that — if you’re brave and ingenious, you can take it from here.