udemy.com - best training bargain out there

This blog is primarily related to SAS programming. However, there are times when we need to expand our horizons to topics that are tangentially related to SAS. One of those items for me is to better understand and implement HTML and CSS as it relates to using SAS stored processes.

While SAS does supply a built in prompting framework to create interfaces, it is very limiting and inflexible. Of course, the way around this is to roll your own via HTML, CSS and JavaScript as needed. If you utilize the _WEBOUT stream destination in a file statement you can create whatever you like.

So the question becomes, how do you learn HTML and CSS to get started? I live near a very nice and respected community college and they offer a course on HTML/CSS that is 40 hours of classroom material. The cost for this course is $1,199.00 which is not exactly cheap, but may be in contrast to that offered at a major university.

As Mick Jagger so eloquently put it in the song Street Fighting Man, "Well, then what can a poor boy do Except to sing for a rock 'n' roll band 'Cause in sleepy London town There's no place for a street fighting man No Hey!".

The answer is the online course Modern HTML & CSS From The Beginning (Including Sass) by Brad Traversy via udemy.com. Brad Traversy is a truly amazing talent who has put out over 650 free videos on his youtube channel - Traversy Media that has over 600,000 followers. This particular course is 21 hours of material and is rated 4.8 out of 5 based on 1,173 reviews. If you look, you will be able to purchase this $149.99 class for as little as $11.99 which is 99% less than the week long class mentioned above. The nice thing is that you can take this course on your own schedule, as time permits - it does not expire. Udemy.com also offers hundreds of other courses and is just a fantastic bargain and very highly recommended.

Macro %then semicolon

As a creature of habit, I typically write %IF %THEN/%ELSE statements using %DO blocks. However, is a %DO block needed for a one line statement? This blog post answers that question via a series of test conditions. Please understand that this is a very simple example program to illustrate the problem that was experienced.

Test 1 single semicolons

%macro test();
   data mydsn;
      %if &sysver = 9.4 %then x = 1;
      %else x = 0;
      date = date();
   run;
%mend;
%test();

80 date = date();   run;
                    ____
                    22
 
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=.  

That seems odd, but I guess I need to add another semicolon to each of the %IF and %ELSE lines to terminate the macro language statements. Here are the results of that attempt.

Test 2 - using two semicolons

 73         %macro test();
 74            data mydsn;
 75               %if &sysver = 9.4 %then x = 1;;  /* notice use of two semicolons */
 76               %else x = 0;;                    /* notice use of two semicolons */
ERROR: There is no matching %IF statement for the %ELSE.
ERROR: A dummy macro will be compiled.
 77               date = date();
 78            run;
 79         %mend;
 80         %test();

Test 3 - two semicolons on the %ELSE line

%macro test();
   data mydsn;
      %if &sysver = 9.4 %then x = 1;  /* one semicolon here */
      %else x = 0;;                   /* two semicolons here */
      date = date();
   run;
%mend;
%test();

NOTE: The data set WORK.MYDSN has 1 observations and 2 variables.

While the use of one semicolon on the first line and two on the second macro language line works, I find it confusing. In the name of ease of reading and maintaining, I still prefer the use of %DO blocks.

Test 4 - %DO blocks

%macro test();
   data mydsn;
      %if &sysver = 9.4 %then %do;
       x = 1;
      %end;
      %else %do;
       x = 0;
      %end;
      date = date();
   run;
%mend;
%test();

NOTE: The data set WORK.MYDSN has 1 observations and 2 variables.

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)

SQL - Joining tables witout a common key

It is always best to allow a remote database to handle as much work as possible and return as little data as possible over the network. SQL is a very universal language used by most, if not all database systems.

Recently I had occasion to extract data from SQL Server and found it most efficient to utilize its system tables to do just that. In my case, I wanted both the total record count as well as the number of character and numeric data types. While this information is available using SAS dictionary tables from an ODBC libname statement, it was taking up to four minutes to return the data.

The code below creates two derived tables that contain the desired information. The problem was that there is not a common key to perform the required join. While a cross-join is possible, those joins in PROC SQL write a WARNING to the SAS log which is not allowed in my parselog macro.

The solution was to join the derived table using the ON 1 = 1 syntax. This worked and returned the correct answer in under one second which is much faster than the four minutes using a libname.

proc sql;
   connect to odbc(dsn=mydsn authdomain="myauth");
      create table results as
         select        totalrows
                     , char_cnt
                     , num_cnt
         from connection to odbc(
            select     cnt.totalrows
                     , col.char_cnt
                     , col.num_cnt
            from
            ( select   sum(p.rows) as totalrows                   
              from     sys.tables t
              join     sys.partitions p
                on         t.object_id = p.object_id
                       and p.index_id IN (0, 1)
              where    t.name = 'balancechanges' 
            ) as cnt
            join
            ( select   
                       sum(iif(ty.name in('nvarchar', 'varchar', 'char'
                                        , 'nchar', 'text'), 1, 0)) as char_cnt
                     , sum(iif(ty.name in('nvarchar', 'varchar', 'char'
                                        , 'nchar', 'text'), 0, 1)) as num_cnt
              from     sys.tables t
              join     sys.columns c
                on     t.object_id = c.object_id
              join     sys.types ty
                on     c.user_type_id = ty.user_type_id
              where    t.name = 'balancechanges'
            ) as col
              on       1 = 1 
       );
   disconnect from odbc;
quit; 

%juliandate macro function

Astronomers use it and so too does the Google search daterange operator (e.g. daterange:2436900-2436934). The Julian calendar starts at noon universal time (UT), 1 January 4713 BCE (before current era or BC), which is before recorded history. In the business world, Julian date has represented the year and sequential day of that year, now known as an ordinal date. Ever since 1988, the ISO 8601 defines current Julian date usage as astronomers use it.

SAS uses the Gregorian calendar which starts on 15 October 1582 to take into account the inaccuracies of the Julian calendar. According to this article, the Gregorian calendar was developed as a refinement of the Julian calendar, shortening the average year by 0.0075 days to stop the drift of the calendar with respect to the equinoxes. To deal with the 10 days of accumulated drift, the date was advanced so that 4 October 1582 was followed by 15 October 1582.

Of course, SAS uses January 1, 1960 as its days zero (0) so adding 2436934.5 days creates the Julian date. SAS does not have any function to return the ISO 8601 or Julian date rather all Julian type functions from SAS relate to ordinal dates. And according to this SAS knowledge base link, "there are no plans to change the names or functionality" of the SAS functions, informats or formats related to Julian dates.

So if you ever need to obtain a Julian date, just use the below code. The Julian date for 14 August 2018 is 2458344.5.

%macro juliandate(date = %sysfunc(date()));
   %sysevalf(&date + 2436934.5)
%mend;