In this issue:

• Manage Your Macros With MFILE

• ASAP Does Macros

• Addressing Architecture

• IT Enhanced

• Use Your Vote

• Be Of Good Type

• Linking Your Tables

• SAS(r) With Style

• Learn More About SAS Software

• Diary

• Miscellany - Joining, leaving, back-issues, and legal bits

A merry Christmas and happy new year from all of us here at RTSL. NOTE: will be back in the new year with more fresh and interesting articles about your favourite software tool. In the meantime, enjoy this issue's mixed bag.

The Integration Technologies article in the last issue caused a larger than usual response. The majority of emails can be summed-up as "are you sure this is really true?". I can assure you that the content of the article is true - we checked it with SAS staff in the UK and the USA. Some of the less sceptical among you were tempted to a) question the architecture of the approach, or b) suggest enhancements. Those comments are addressed in the Addressing Architecture and IT Enhanced articles respectively.

NOTE: is free. If you find it useful, please forward it to your friends, colleagues, and clients. Encourage them to take-out their own subscription by sending a plain text email to listmgr@ratcliffe.co.uk with a subject of "subscribe-note.colon" (without the quotes). Or just click here.

-Andrew
note.editor@ratcliffe.co.uk

One of the great banes of the lives of those who create macros is the refusal of SAS software to reveal useful information about their execution. An example log is shown below. Which of the lines in the DATA step is causing the NOTE? In other words, var1 and var2 are both expected to be numeric, but one of them is not - which one?

41 %macro demo(data=);

42 data _null_;

43 set &data;

44 x=putn(var1,'BEST.');

45 y=putn(var2,'BEST.');

46 run;

47 %mend demo;

48

49 options mprint;

50

51 %demo(data=work.indata);

MPRINT(DEMO): data _null_;

MPRINT(DEMO): set work.indata;

MPRINT(DEMO): x=putn(var1,'BEST.');

MPRINT(DEMO): y=putn(var2,'BEST.');

MPRINT(DEMO): run;

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

1:68

The log tells us that the problem is at line number 1, but that is of no meaning. The general solution is to manually extract the code generated by the macro from the log and to manually submit it, as shown below.

52 data _null_;

53 set work.indata;

54 x=putn(var1,'BEST.');

55 y=putn(var2,'BEST.');

56 run;

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

55:8

We can now clearly see that var2 is the culprit - we expected it to be numeric, but it is actually character. For a simple macro like %demo, that's a practical solution, but for large macros it becomes very time-consuming (and error-prone) when you have to remove the line numbers and the NOTEs before you can execute it.

But fear not, help is at hand. Version 8 introduced a feature that had been available unofficially for some time in V6 (albeit with a different name) - the MFILE option. The MFILE option, when used in conjunction with the MPRINT option and an assignment to a filename of MPRINT, writes the code generated by your macros to a external file. And, unlike the code shown in the log, it doesn't have line numbers, NOTE statements, and all those other inconvenient things that the log has. For the code shown above we might use the following.

options mprint mfile;

filename mprint 'c:\temp\democode.sas';

The MFILE option doesn't make macro debugging easy, but it does make it easier!

Return to top...

In addition to the large response we got with respect to the Integration Technologies article, we also got a tremendous amount of interest in complementSoft's ASAP - the productivity tool for SAS programmers. If you haven't already seen it, take a look at the complementSoft web site (www.complementSoft.com) and see how ASAP can automatically create process flow diagrams and data flow diagrams from your SAS code. That's in addition to the integrated code editor (with colourisation), the ability to execute your SAS code locally or remotely, automatic checking of the resulting log for warnings and errors, and the provision of a code template library. But I guess I would say that because, as I mentioned in the previous issue of NOTE:, RTSL are the European distributors of ASAP.

ASAP does a good job of diagramming the code inside your macros, but it cannot completely describe your macros because the code generated by complex macros can only be fully resolved at run-time. That's where the MFILE option comes into play. You can use the MFILE option to capture the code generated by your complex macros, and then get ASAP to produce diagrams of the captured code. Very useful.

Return to top...

The example code in the last issue that showed how to use SAS Integration Technologies (IT) from Visual Basic for Applications (VBA) was intended solely as a short, simple example. It had a number of shortcomings, not least was the fact that it embedded SAS code within the VBA code. This is not best practice because:

• the VBA code addresses the presentation layer of the application

• the SAS code addresses the business logic layer of the application

• VBA coding and SAS coding are different skills

• the coding may be done by different programmers

For all of the reasons detailed above, it is preferable to store the SAS code independently of the VBA code. In a client/server environment this is all the more important because the client and server may be in separate geographic locations. You can easily achieve the desired separation by replacing the SAS code within the VBA with a simple %include that points to the desired SAS program. The downside of this simple approach is that the client requires knowledge of the location of the SAS code, so we haven't achieved complete decoupling of the presentation layer from the business logic layer.

SAS provides an alternative approach called Stored Processes (SP). Using SP we can replace the hard-coded SAS code with an indirect reference to it, and we can store the SAS code independently:

Rem submit some SAS code

Dim sp As SAS.StoredProcessService

Set sp = obWS.LanguageService.StoredProcessService

sp.Repository = "file:/SASPrograms/StoredProcesses"

sp.Execute "note08", _

     "maxX=20 multiple=2.5"

In the above example we have stored our SAS code in a "file repository" at c:\SASPrograms\StoredProcesses, and we have named our program note08.sas. In addition, we have made use of SP's ability to pass parameters. In this instance we have specified two parameters: maxX and multiple. These parameters are defined and used in our SP as shown below:

%let maxX= ;

%let multiple= ;

*ProcessBody;

data a; 

  do x= 1 to &maxX; 

    y=&multiple*x; 

    output; 

  end; 

run;

The parameters are listed as null macro value assignments at the top of the program, then the special keyword comment "*ProcessBody;" is coded - the remainder of the file is a straight-forward SAS program. When the SP is executed, the values specified in the sp.execute statement are substituted into the macro variables and used in the subsequent program.

Using SP in this way does not represent a major improvement over %include because we have still specified the physical location of the SAS program, and we could have set those macro variables too. However, it is possible to use the IT Administrator facility to define the repository in an LDAP directory and thereby remove the need for the client layer to have knowledge of the physical location of the business logic.

For more information on IT, take a look at SAS's excellent Enterprise Integration Community pages at www.sas.com/rnd/eai. You will find papers, samples, downloads, and more besides (including the basis for these articles).

Return to top...

Mark Tabladillo had some comments on the IT article too. Here's what he said:

"I am sending a modification for the Integration Technologies code circulated in the October 2001 newsletter. The change is made because 'adCmdTableDirect' is only ADO 2.0 and above, and only for OLE DB providers which support direct table references and SQL. Perhaps the original code works on some software configurations, but it only returned the first row for me. I additionally added some string code which helps illustrate the original point."

And here is Mark's changed code:

Dim obWS As SAS.Workspace
Dim obWSM As New SASWorkspaceManager.WorkspaceManager


Sub Form_Load()

Dim obConn As New ADODB.Connection
Dim obRS As New ADODB.Recordset
Dim errorString As String

Rem start the SAS session
Set obWS = obWSM.Workspaces.CreateWorkspaceByServer("Local", _
   VisibilityProcess, Nothing, "", "", errorString)

Rem submit some SAS code
obWS.LanguageService.Submit _
"data a; do x=1 to 10; y=10*x; output; end; run;"

Rem open an ADO connection to the data set
Dim connString As String
connString = "provider=sas.iomprovider.1; SAS Workspace ID=" _
   + obWS.UniqueIdentifier
obConn.Open connString

Rem modified by markTab -- change recordset options to adCmdTable
obRS.Open "work.a", obConn, adOpenStatic, adLockReadOnly, adCmdTable

Rem write the table as html (visible in Word with tags)
obRS.MoveFirst
Dim sTable As String

Rem modified by markTab -- add vbCr's and vbTab's
sTable = "<TABLE BORDER=0><TBODY>" & vbCr & "<TR>" & vbCr & vbTab _
   & "<TD class=Data>"
Selection.TypeText sTable

Rem modified by markTab -- add vbCr's and vbTab's
sTable = obRS.GetString(, , "</TD>" & vbCr & vbTab & "<TD class=Data>", _
   "</TD>" & vbCr & "</TR>" & vbCr & "<TR>" & vbCr & vbTab & "<TD class=Data>")

Rem modifed by markTab -- truncate string to adjust for last record
If Len(sTable) > 21 Then sTable = Mid$(sTable, 1, Len(sTable) - 21)

Selection.TypeText sTable

Rem -- modified by markTab -- new closing string assuming truncation
sTable = "</TBODY></TABLE>"
Selection.TypeText sTable

Rem tidy-up
obRS.Close
obConn.Close
obWS.Close

End Sub

If you try Mark's code you will see that it produces much neater HTML due to his use of carriage-return and tab characters: vbCr and vbTab. Thanks for your comments, Mark.

Return to top...

It's that time of year again - time for the SASware ballot.

Every year, SAS polls its customers for their suggestions on new and enhanced features. This is the SASware ballot. And this is no PR exercise that just gets thrown in the bin. SAS claims that more than 85 percent of the top items on the SASware Ballot have been implemented by their Research and Development Division.

To have your say, just go to the SASware Ballot site at www.sas.com/sasware. Don't delay, voting ends on January 4th 2002. The results will be posted on the SAS web site later in the new year.

Return to top...

In the SAS With Style section of the last issue, we showed the use of the PUTN function to convert a numeric variable to character - and thus avoid a message from SAS regarding type-conversion. Some of you asked for more detail on the subject, so we're happy to oblige.

When we talk about a variable's type in SAS, we are referring to whether it is character or numeric. Other languages have a greater range of types, but the Base SAS language is limited to just these two. The two types have very different purposes and most functions and operators in SAS demand that their parameters be of a specific type. For instance, in most languages, neither of the following are valid and would produce errors:

x = 'a' + 'b';

y = 1 !! 2;

It doesn't make sense to try to add-up two strings, and it is not strictly possible to join together two numbers. But, unlike most languages, SAS will allow both of the preceding statements without error. It will, however, issue a number of notes:

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

2:5 2:11

NOTE: Numeric values have been converted to character values at the places given by:

(Line):(Column).

3:5 3:10

NOTE: Invalid numeric data, 'a' , at line 2 column 5.

NOTE: Invalid numeric data, 'b' , at line 2 column 11.

x=. y=1 2 _ERROR_=1 _N_=1

NOTE: Missing values were generated as a result of performing an operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

1 at 2:9

SAS is able to make an effort at executing the code by spotting the programmer's mistake and converting the character values to numeric (so that it can try to add them up) and converting the numeric values to character (so that it can concatenate them).

A more practical example is shown below:

14 data _null_;

15 x = 1;

16 y = 'Day#' !! x;

17 z = 'Day#' !! left(x);

18 put x= y= z=;

19 run;

NOTE: Numeric values have been converted to character values at the places given by:

(Line):(Column).

16:17 17:22

x=1 y=Day# 1 z=Day#1

It is quite reasonable to try to create the string 'Day#1' by concatenating the prefix with x, but x is numeric and the concatenation operator demands that both operands be character, so SAS converts x's value to be a character and then does the concatenation (and it issues a note to tell you what it has done). So the end result is what the programmer intended, except that we have an untidy message in the log.

For the purposes of helping the programmer to spot bugs, it is advisable to keep the log as tidy as possible so that any unexpected messages won't be lost in a see of "normal" messages. And getting rid of the note shown above is easy, we use the PUTN function:

20 data _null_;

21 x = 1;

22 z = 'Day#' !! left(putn(x,'BEST.'));

23 put x= z=;

24 run;

x=1 z=Day#1

And if you need to convert a character value to numeric, just use INPUTN:

30 data _null_;

31 x = '1';

32 y = 3 + x;

33 z = 3 + inputn(x,'BEST.');

34 put x= y= z=;

35 run;

NOTE: Character values have been converted to numeric values at the places given by:

(Line):(Column).

32:11

x=1 y=4 z=4

So, if you want to keep your log clear of unnecessary messages, and you want to make your code more specific about what it's doing, and show your fellow programmers that you are fully in control of your code, use PUTN to convert numeric to character, and use INPUTN to convert character to numeric.

Return to top...

Earlier this year we did some work in North Wales - a beautiful part of Britain. Our client had some PROC TABULATEs that were creating HTML tables. The client wanted us to add some more TABULATEs to provide higher-level summary information. And in addition, the client wanted the new TABULATEs to provide hyperlinks to the detail tables.

SAS's Output Delivery System (ODS) makes it wonderfully simple to create neat and attractive HTML output from your SAS procedures, not least TABULATE. And, if you have a BY statement in your PROC, you can ask ODS to create a separate table for each BY value and to produce a table of contents linking them. Take a look at the code below.

data citiday2;
  set sashelp.citiday;

  year=year(date);
  qtr=qtr(date); /* For use in summary */
  month=month(date);
  day=day(date);
run;
ods listing close;
ods html path='c:\temp' body='atab.htm' frame='aframe.htm' contents='atoc.htm';
proc tabulate data=citiday2; 
  by year;
  class month day;
  var snydjcm;
  table month, day*snydjcm;
run;
ods html close;
ods listing;

This code produces three html files. The atab.htm file contains the five tables produced by TABULATE. The atoc.htm file contains a link to each of the five tables. The aframe.htm file contains a page with an HTML frame that displays atoc.htm in the left-hand pane, and atab.htm in the right-hand pane.

Our task was to create a TABULATE showing an overall summary. Continuing the example above, our overall summary was quickly created thus:

proc tabulate data=citiday2;
  class year qtr;
  var snydjcm;
  table year, qtr*snydjcm;
run;

But how to link the years in the overall summary with the individual yearly tables? Each of the yearly tables has an anchor value that the atoc.htm page uses to link to them. A quick look at atoc.htm shows the style of the anchors: #idx, #idx1, #idx2, #idx3, and #idx4. We can influence this with the anchor parameter on the ODS statement.

SAS doesn't directly give us the ability to do the hyperlinking that we need, but we can easily do it ourselves by changing the values of the years to include a small smattering of HTML. The following does the whole task:

data citiday2;
  set sashelp.citiday;
  year = year(date);
  qtr = qtr(date); /* For use in summary */
  month=month(date);
  day=day(date);
  select(year); /* For use in summary */
    when(1988) htmlyear='<A HREF="c:\temp\atab.htm#note1">1988</a>';
    when(1989) htmlyear='<A HREF="c:\temp\atab.htm#note2">1989</a>';
    when(1990) htmlyear='<A HREF="c:\temp\atab.htm#note3">1990</a>';
    when(1991) htmlyear='<A HREF="c:\temp\atab.htm#note4">1991</a>';
    when(1992) htmlyear='<A HREF="c:\temp\atab.htm#note5">1992</a>';
  end;
run;

ods listing close;
ods html path='c:\temp' body='atab.htm' frame='aframe.htm' contents='atoc.htm' anchor='note1';

proc tabulate data=citiday2;
  by year;
  class month day;
  var snydjcm;
  table month, day*snydjcm;
run;

ods html close;
ods html body='c:\temp\aindex.htm';

proc tabulate data=citiday2;
  class htmlyear qtr;
  var snydjcm;
  table htmlyear, qtr*snydjcm;
run;

ods html close;
ods listing;

If you try running the code you will see that aindex.htm gives you the summary table and that the years offer hyperlinks to the more detailed table created by the TABULATE with the BY statement. The output has a lot of rough edges, but the purpose of this article was to demonstrate the hyperlinking; and the code is not ideal because it has the years hard-coded in it, but we will save the tidying-up for another article!

This article has demonstrated how it is possible to use a small bit of knowledge about HTML to do your own hyperlinking and to thereby increase the value of your SAS output. We have also seen how SAS itself provides some degree of hyperlinking. As ever with SAS, there is more than one way to produce similar output, you can choose which approach suits your needs best.

Return to top...

One of the less-attractive aspects of a programmer's life is maintaining existing code. Most programmers would prefer to be creating something new rather than manipulating old code. But maintaining old code is a necessity, be it your own code, or somebody else's. And in those circumstances, you will be grateful if the code has been written in a neat and clear fashion.

Comments are a critical part of your programs, and they can be used in many different ways. I like to encourage the use of "overview" blocks at the top of large sections (a whole program counts as a "large section"). The individual lines of the overview then get used to head-up the respective sections of code. The code might look like this:

/******************************************************/

/* 1. Get subset of the demog info                    */

/* 2. Get subset of the lab info                      */

/* 3. Get subset of the meds info                     */

/* 4. Merge demog, lab, and meds and transpose result */

/* 5. Create final transport file                     */

/******************************************************/

/***********************************/

/* 1. Get subset of the demog info */

/***********************************/

  code to do the demog subseting

/***********************************/

/* 2. Get subset of the lab info   */

/***********************************/

  code to do the lab subseting

/***********************************/

/* 3. Get subset of the meds info  */

/***********************************/

  code to do the meds subseting

/******************************************************/

/* 4. Merge demog, lab, and meds and transpose result */

/******************************************************/

  code to do the merge and transpose

/***********************************/

/* 5. Create final transport file  */

/***********************************/

  code to do the proc cport

The overview block gives any maintenance programmer a great outline of the program and also acts as some kind of index. A general rule of thumb is to have between 6 and 12 sections (yes, I know the example breaks the rules). If the code in any of the sections is large, consider using a secondary level overview block to break it down further.

This style of commenting simply follows the oft-quoted rule of divide and conquer - break down your problem into small, manageable pieces and solve each of them in turn.

Return to top...

Heidelberg, Germany plays host to Europe's top technical conference - SASTEK. Held between the 29th and 31st of January, SASTEK is an excellent opportunity for European SAS practitioners to increase the depth of their SAS knowledge. More information is at www.sas.com/usergroups/eur/sastek/.

Traditionally restricted to SAS partners, the 2002 event has been opened-up to non-partners too.

SUGI 27 registration is now open. Details are on the web site at www.sas.com/usergroups/sugi/sugi27 - including online registration and accommodation details.

SUGI (SAS User Group International) is an excellent conference for learning more about how to be productive with SAS software. The 2002 event is being held at Walt Disney World, Florida. Highlights of this year's SUGI include:

• The latest information from SAS about Version 9

• Special Sunday Seminar: "Changes and Enhancements to Version 9 - a Hands-on Workshop"

• Enhanced Futures Forum - your chance to quiz the technical powers that be at SAS

• Informative tutorials, hands-on workshops, and other exciting paper presentations

Additional information about SUGI 27 is available on the SUGI 27 Web Site at www.sas.com/usergroups/sugi/sugi27.

Return to top...

If we were not constrained by the need to earn money by working(!), this diary provides a guide to some of the world-wide events we might like to attend. Those marked with an asterisk are those that we will be attending. If you plan to go to any of the listed events, please let us know - we'd be very grateful for any comments after the event. And it would be nice to meet if we are going too.

January 2002
29th - 31st, SASTEK, Heidelberg, Germany. Premier European technical SAS conference.*

April 2002
14th - 17th, SUGI (SAS User Group International), Florida, USA. Premier SAS conference world-wide.

May 2002
26th - 29th, XP2002 (eXtreme Programming), Sardinia, Italy Premier European conference on eXtreme Programming and flexible processes in software engineering.

June 2002
11th-13th, SeUGI (SAS Users Group International in Europe), Paris, France. Premier SAS conference in Europe.

October 2002
14th - 18th, OOPSLA, Florida, USA. Premier object technology conference world-wide

.March 2003
30th - 2nd April, SUGI (SAS User Group International), Seattle, USA. Premier SAS conference world-wide.

Return to top...

You can subscribe and unsubscribe by visiting our web site at www.ratcliffe.co.uk. Or, to subscribe just click here and hit the Send button in your email client; and to unsubscribe, just click here and hit the Send button in your email client. Please do not include a message in the body of the email, it will not be seen by a human!

Back issues are available on our web site at www.ratcliffe.co.uk.

Please send comments to note.editor@ratcliffe.co.uk.

NOTE: (c) 2001 Ratcliffe Technical Services Limited. All rights reserved.
5 Willow Close, Bexley, Kent, GB-DA5 1QY, England.

Republication by permission only. You may forward copies only if no fee is involved. Please encourage the recipients of the forwarded copy to subscribe and get their own free copy of NOTE:.

NOTE: is a production solely of RTSL and has no affiliation with any other organisation.

This edition of NOTE: was sent free to 490 subscribers worldwide.

This is a valid HTML 4.01 Transitional document

Return to top...