Wednesday, January 23, 2013

SAS: Merge has one END only!

SAS' Merge has one END only.  The indicated variable is set to 1 when both files are at end of file...

    data Work.Updated (keep = Store Date Cost) ;
    merge Work.Master (rename = (Cost = m_Cost) in=in_master)
        Work.Trans (rename = (Cost = t_Cost) in=in_trans )
        end=eof_both ;
    by Store Date;

...and later...

    if (eof_both) then do;
        * do eof stuff ;
    end;  
    run;


SAS: Setting a macro variable to value of SYSCC, which is itself a macro variable.

Here's how to set a SAS macro variable to the value of SYSCC, which is itself a macro variable:

%if (&SYSCC ne 0) %then %do;
   %let ERR_LVL = %sysevalf(&SYSCC);
%end;

Tuesday, January 22, 2013

SAS: Left-align a macro value

I write various data to a process log as name-value pairs.  Sometimes the "values" are character and sometimes they are numeric, so they are defined as character in the database.  I want the "values" to be left-aligned regardless of type.  The name-value pairs are appended to the log via a macro which uses an INSERT statement within PROC SQL.  For some unknown reason, I was unable to use trim(left(x)) in the INSERT statement.  But I stumbled upon a format option which allows left / right / center justification:

%append_to_process_log("#output", put(&OUT, 10. -l));


SAS: Redirecting the WORK files

Here's how to send SAS WORK files to a different drive / folder.

Edit the file (your drive may differ...)
E:\SAS\Config\Lev1\SASApp\sasv9_usermods.cfg

Add these two lines (the first of the two may already be there; if so then the line after as shown here:0
/* Setup the default SAS System user work folder */
-WORK "J:\SASWORK"

Note there is no semi-colon on the -WORK line, which is unlike most SAS stuff.



Monday, January 21, 2013

SAS: log1px funtion

Here's some SAS information I found curious.  I am currently taking a time series course, and in the first homework assignment we are asked to calculate the log returns as lnreturn = log(ret + 1).  This must be a common operation, because SAS has a builtin function for that purpose.  The function is log1px and is used as follows:  lnreturn = log1px(ret);

Big deal, right?  I mean it's not as if the other way (log(ret + 1)) is hard to code.  Yes, but according to the documentation (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003121132.htm), "when x is close to 0, LOG1PX(x) can be more accurate than LOG(1+x)."

(I assume the 1PX stands for "1 plus x".)


Tuesday, January 15, 2013

Ext.js - Load data synchronously

Ext.js loading process is asynchronous: it will not wait for the data to be loaded.  Rather, it will continue processing immediately.  So you cannot expect the data you load to be immediately available.  The following will make the load synchronous.

Before

    launch: function()
    {
        // other stuff too...

        loadComboBoxStores();
  
        // other stuff too...
    }

After

    launch: function() 
    {
        // other stuff too...

        Ext.onReady(function()
        {
            loadComboBoxStores();
        }); 

        // other stuff too...
    }

(I post this reluctantly as I have limited understanding of this.  But my solution appears to work.)

post script:  That didn't work.  But this did...

According to the Ext.js 4 documentation, "Ajax server requests are asynchronous and this call (to get data from server) will return before the response (data) has been received.  Process any returned data in a callback function."

So use of callback is the key.  Callback is an optional parameter of the store.load().

Here is my first (and apparently successful) attempt at a callback function.  Well, actually two callback functions.  First, it dynamically loads a list of stores of combo boxes.  Then it dynamically loads the contents of each of those stores of combo boxes.

There may be better ways of doing this, but it does work...

// Build stores for combox boxes

function loadComboBoxStores()
{
    // Ajax server requests are asynchronous, meaning the call to Ajax will return immediately,
    // before the response from the server has been received.  Returned data should be processed
    // in a callback function.  (There are all sorts of references to this issue on the web.)
   
    // create Model which can be used for all combo boxes
    Ext.define('MyComboBoxModel', {
        extend: 'Ext.data.Model',
        fields: [
            {name: 'value'  , type: 'string'},
            {name: 'display', type: 'string'}
        ]
    });

    var storeOfComboBoxStoreNames = Ext.getStore('ComboBoxStoreNames');
   
    storeOfComboBoxStoreNames.load({
        callback: function()
        {
            for (var i = 0; i < storeOfComboBoxStoreNames.getCount(); i++)
            {
                var row = storeOfComboBoxStoreNames.getAt(i);
                // console.log("store: " + row.data.store);
                Ext.create('Ext.data.Store',
                {
                    storeId: row.data.store,
                    model  : 'MyComboBoxModel'
                } );
            }
           
            var storeOfComboBoxes = Ext.getStore('ComboBoxes');

            storeOfComboBoxes.load({
                callback: function()
                {
                    for (var i = 0; i < storeOfComboBoxes.getCount(); i++)
                    {
                        var row = storeOfComboBoxes.getAt(i);
                        var store = Ext.getStore(row.data.store);
                        var model = store.model;
                        var record = new model( { value: row.data.value, display: row.data.display } );
                        store.add(record);
                    }
                }  // end inner callback
            });    // end inner load
        }  // end outer callback
    });  // end outer load
}







Saturday, January 12, 2013

SAS: Reading and Writing XML

SAS' xmlv2 libname engine makes it easy to read and write XML files!

Here's my source code:


    * Create test SAS dataset ;
    data work.kids;
    input name $ age gender $;
    datalines;
    Kamina 6 F
    Raelani 4 F
    Elliott 1 M
    ;
    run;

    libname myxml xmlv2 "C:\temp\kids.xml";

    * Write SAS data to XML ;
    data myxml.kids;
    set work.kids;
    run;

    * Read XML data into SAS;
    data work.getback;
    set myxml.kids;
    run;

    * Show what you got;
    proc print data=work.getback;
    run;


Here's the output from PROC PRINT:

Obs name age gender
1 Kamina 6 F
2 Raelani 4 F
3 Elliott 1 M


Here's the XML that was written, and then read back in:

    <?xml version="1.0" encoding="windows-1252" ?>
    <TABLE>
       <KIDS>
          <name>Kamina</name>
          <age>6</age>
          <gender>F</gender>
       </KIDS>
       <KIDS>
          <name>Raelani</name>
          <age>4</age>
          <gender>F</gender>
       </KIDS>
       <KIDS>
          <name>Elliott</name>
          <age>1</age>
          <gender>M</gender>
       </KIDS>
    </TABLE>




SAS: Unwanted blank in PUT output

Check this out!  When using the PUT statement, if a variable is followed by a literal, you always get a blank between the two, but not so when a literal is followed by a variable. In the former case, if you don't want that blank there, you must use the column modifier +(-1) to "back up" one space.  Hardly what I would call intuitive!

Here's some source code:

    data _null_;
    a = 123;
    b = -123;
    put "[" a "]";
    put "[" b "]";
    put "[" a +(-1) "]";
    put "[" b +(-1) "]";
    run;


And here's the output:

    [123 ]
    [-123 ]
    [123]
    [-123]




Friday, January 11, 2013

SAS: PROC CONTENTS list variables in order created

I have often been annoyed that SAS' PROC CONTENTS lists variables in alphabetical order rather than the order in which they appear in the dataset.  Turns out there is an option, varnum, which causes the variables to be listed in my preferred order.  Here's some sample code:

    * Demo varnum option on PROC CONTENTS ;
    data work.kids;
    input name $ age gender $;
    datalines;
    Kamina 6 F
    Raelani 4 F
    Elliott 1 M
    ;
    run;

    proc contents data=work.kids;
    run;

    proc contents data=work.kids varnum;
    run;


And some (truncated) output...

    Alphabetic List of Variables and Attributes

           #    Variable    Type    Len

           2    age         Num       8
           3    gender      Char      8
           1    name        Char      8



            Variables in Creation Order

           #    Variable    Type    Len

           1    name        Char      8
           2    age         Num       8
           3    gender      Char      8



Thursday, January 10, 2013

HTML: Width of a paragraph

Here's how to set the width of a paragraph in HTML:  <p style="width:80%;">



Wednesday, January 9, 2013

SAS: Get environment variables using %sysget

%let UserID=%sysget(USERNAME);
%put User is &UserID;

Environment variable (USERNAME here) is case sensitive.

Annoying SAS "Unsupported Device" message in Enterprise Guide

I hate it when SAS Enterprise Guide shows a warning, and the only message is this:

WARNING: Unsupported device 'ACTIVEX' for PDF destination. Using device 'ACTXIMG'.

This can be eliminated by putting this option at the top of the program:

options dev=actximg;  * Prevents the annoying "Unsupported device" message in EG ;


Monday, January 7, 2013

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

My web app was failing with this exception:  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException.  Turns out it was using a database connection pool (see article at http://www.developer.com/java/data/article.php/3847901/Implement-Java-Connection-Pooling-with-JDBC.htm) and the connections were timing out.  Default timeout on MySQL is eight hours.  I fixed this by using the isValid method of the Connection class (code added to getConnectionFromPool method):

try
{
   if (connection == null || !connection.isValid(5))
   {
      connection = createNewConnectionForPool();
   }

}
catch (SQLException sqle)
{
   connection = createNewConnectionForPool();

}

return connection;

Creating a background process in DOS

Creating a background process in Unix is trivial: put a trailing ampersand on the command.  But what about DOS (Windows)? Use the START command as explained in this article:  http://forums.whirlpool.net.au/archive/391278

C:\>start notepad.exe
will start Notepad, and DOS prompt is available for next command

C:\>start /wait notepad.exe 
will start Notepad, and DOS prompt is not available until Notepad is closed.

Wednesday, January 2, 2013

SAS: Continue processing despite errors

I have a SAS macro, used at the end of many programs, to update a submissions file indicating when a program has completed processing and the error code.  But this macro wouldn't work if the program failed.  And yet the log made it look like it was doing something there.  I was unaware of the NOSYNTAXCHECK option. I think the name is misleading.  What it really means is "don't just syntax check".

Here is a link to the SAS documentation:  http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#base-sysop-syntaxcheck.htm

"If a syntax or semantic error occurs in a DATA step after the SYNTAXCHECK option is set, then SAS enters syntax check mode, which remains in effect from the point where SAS encountered the error to the end of the code that was submitted. After SAS enters syntax mode, all subsequent DATA step statements and PROC step statements are validated. While in syntax check mode, only limited processing is performed."

So it was doing some "limited processing", just as the log seemed to indicate (such as resolving macro variables).

Another misleading entry on the log was that DATA steps were executed, but no output written.  I guess they were just syntax checked.

So I modified my macro as follows:

%macro updsub;
options nosyntaxcheck;  * processing regardless of previous errors ;
/* my macro code here */
options syntaxcheck;   * put it back as it (probably) was ;
%mend updsub;

Problem solved!