Enterprise Guide Keyboard Macros

Enterprise Guide (EG) supports keyboard macro abbreviations to write out snippets of code directly into a program editor. This makes it easy to supply preset code in a consistent manner without having to remember the syntax.

From the EG menu, select Program | Add Abbreviation Macro… or use Ctrl+Shift+A. Use the below dialog to enter your own abbreviation (e.g. _sql) and associated code in the Text to insert: section.

You can delete, edit, rename or export existing abbreviations or import existing keyboard macro functions (.KMF extension) by selecting Program | Editor Macros | Macros from the menu.

Below is the resulting output of the _hash keyboard macro abbreviation.

data outdsn;
   if 0 then set hashdsn; /* Prime the PDV */

   dcl hash h(dataset: "hashdsn");
   h.definekey('keyvar');
   h.definedata('keyvar1', 'datavar1');
   h.definedone();

   do until(eof);
      set otherdsn end = eof;
      if h.find() ne 0 then call missing(keyvar1, datavar1);
      output;
   end;
   stop;
run;

ColumnLength macro function

There are times when you need to dynamically obtain the data type and length of a SAS data set column. This occurred for me when I was writing a generic post process following a proc means with a conditional class statement. I need to know the data type and length of the class variable so that it can be assigned in the ATTRIB statement.

The below code utilizes the %isblank macro function that can be found here. The results of the function will be a dollar sign ($) if it is a character data type followed by the length of the data set column.

The table reveals the various results of calling this macro:

Call	Result	Message
%columnlength(dsn=class, column = age);	8
%columnlength(dsn=class, column = name);	$8
%columnlength(dsn=classx, column = name);		ERROR: The data set classx does not exist.
%columnlength(dsn=class);		ERROR: You must pass in a value for column.
%columnlength(dsn=class, column = namex);		ERROR: Could not find column: namex

Here is an example of how I used this. I have a macro that includes a proc means/summary with a conditional class statement. After the process is finished I want to control the formatting, order and even add a new column. So this is my code snippet

data &dsn;
   attrib
       yyyymm length = $6
   %if not %isblank(&classvar( %then %do;
      &classvar length = %columnlength( dsn = &dsn, column = &classvar)
   %end;
   ...

%macro columnlength( 
     dsn    = 
   , column = 
);

   %local
      dsid
      position
      type
      retval
   ;

   %if not %sysfunc(exist(&dsn)) %then %do;
      %put %str(E)RROR: The data set &dsn does not exist.;
      %return;
   %end;

   %if %isblank(&column) %then %do;
      %put %str(E)RROR: You must pass in a value for column.;
      %return;
   %end;

   %let dsid = %sysfunc(open(&dsn.));
   %if &dsid %then %do;
      %let position = %sysfunc(varnum(&dsid, &column));
      %if &position = 0 %then %put %str(E)RROR: Could not find column: &column.;
      %else %do;
         %if %sysfunc(vartype(&dsid, &position)) = C %then %let type = $;
         %let retval = &type%sysfunc(varlen(&dsid, &position));
      %end;
      %let dsid = %sysfunc(close(&dsid));
   %end;

   &retval
%mend;

Stacking datasets

A frequent task is to stack SAS datasets segregated by a time period such as a year/month over time to analyze trends. An easy way to do that is to use the colon modifier as a wildcard at the end of a known pattern. That technique is used to read in data sets starting with x_ or in the second example by the more specific x_2017 and x_2018 patterns.

Sometimes you may have this type of yyyymm pattern, but only want to read in for a set period of time such as 201711 to 201802. The third example uses a macro to handle that scenario. Of course this macro can be augmented to support a where clause or to drop/keep a list of variables.

proc datasets lib = work nolist kill;
quit;

data x_201711 x_201712 x_201801 x_201802;
   call streaminit(1);
   do i = 1 to 4;
      state = fipnamel(ceil(rand('uniform')*50));
           if i = 1 then output x_201711;
      else if i = 2 then output x_201712;
      else if i = 3 then output x_201801;
      else if i = 4 then output x_201802;
   end;
run;

data stack1;
   set x_:;
run;
 
data stack2;
   set x_2017: x_2018: ;
run; 
 

%macro stackds(
     dsnin=
   , dsnout=
   , startyyyymm=
   , endyyyymm=
   , where_clause=
   , drop=
);

   %let start      = %sysfunc(inputn(&startyyyymm.01, anydtdte9.));
   %let end        = %sysfunc(inputn(&endyyyymm.01, anydtdte9.));
   %let diff       = %sysfunc(intck(month, &start, &end));
   
   data &dsnout;
      set 
      %do i = 0 %to &diff;
         %let yyyymm = %sysfunc(putn(%sysfunc(intnx(month,&start,&i,b)),yymmn6.));
         &dsnin&yyyymm
      %end;
      ;
      %if &where_clause ne %then %do;
         where &where_clause;
      %end;
      %if &drop ne %then %do;
        drop &drop;
      %end;      
  run;
 %mend;
 %stackds(
     dsnin = x_
   , dsnout = stack3
   , startyyyymm = 201711
   , endyyyymm = 201802
   , where_clause = state = 'Utah'
   , drop = i)