KeePass2 and Gmail

It's been a while since I posted, but with a good cause: We just had our second child a few weeks ago!

The other day, Google decided to change Gmail's login screen to be in three parts (two for those without two-step authentication): 1. Username 2. Password 3. Two-Step Authentication. This is annoying because I use KeePass2 with auto-type, and the new pages interfere with the auto-typing mechanism.

Today I decided to solve that issue, and I'm posting it here to share with anyone else who may also have the same problem. Here are the steps:

1. Open up the Gmail or Google Apps email account you want to change ("Edit/View Entry" on the context menu, or hit Enter when the entry is selected).
2. Go to the Auto-Type tab.
3. Select "Override default sequence" option.
4. The field should initial have (without quotes):
   
   "{USERNAME}{TAB}{PASSWORD}{ENTER}"
   
   Replace that with (without quotes):
   
   "{USERNAME}{TAB}{ENTER}{DELAY 2000}{PASSWORD}{TAB}{ENTER}"
   
   
5. Test in your favorite browser. Adjust the "{DELAY mmmm}" parameter by replacing "mmmm" with a different numeric value. This is in milliseconds, so a value of 1000 is 1 second.

The "{DELAY 2000}" parameter is the key to fixing the issue. Since there is a new page in between the username and password, we need a delay to let the page load before typing the password.

For more information on the auto-type feature and the parameters that can be used, see the KeePass documentation here:

http://keepass.info/help/base/autotype.html

Don't forget to tag your Gmail account as an OpenSSL account (add "OpenSSL" to the description) while you're at it!

How I Reacted to Heartbleed

Recently a bug was revealed in OpenSSL, called Heartbleed. One of the very unfortunate aspects of this bug was the potential for all passwords and cryptographic keys on a server could have been dumped to any hacker who knew about the flaw. As users, we can't control the cryptographic keys; however, we can control our passwords.

I was able to quickly identify my key accounts at risk in order to reset each password. How? With a password management database, KeePass. I also had help from the well-documented Mashable article.

I have all of my passwords stored in a KeePass database. Each account is categorized into groups like Banking, Email, Investments, etc. (see screenshot above). Using the Mashable article, I edited the each description by adding "OpenSSL" for each account that used OpenSSL. I also ran a few online searches to determine the status of other accounts I wasn't sure about.

Once I finished that, I search through the entire database (over 240 passwords!!) for "OpenSSL" to list all the entries together. Starting with my financial accounts in Banking, Investments, and other categories, I changed each password, one-by-one.

It took about an hour, but who out there without a password management database can say they reset all their key accounts so quickly?

Now I'm not going to stop there. I'll be reseting some passwords once a week until I hear this issue is cleared up. (Thus entering "OpenSSL" permanently in each account description.) It's going to take time to re-issue those cryptographic keys and for all the certificate authorities to synchronize. For these important accounts, I don't want to risk losing control.

In the end, if you are a user of web services like me, start using a password management database right now. (I would not use LastPass since it is online. However, according to their documentation, they use forward secrecy, which is currently the best way to do these things and would prevent any true information leak.)

Start by entering your most important accounts and change the passwords to random passwords. Gradually add those you don't use as frequently or are not as important. Then set a schedule for each important password to expire on a regular basis: every 2 months, 6 months, or whatever you think is best for the account. My financial accounts are reset every 3-6 months.

Hopefully webmasters, server admins, cryptographers, and anyone else involved in this ecosystem starts to realize that we have a broken internet. Encryption technologies are failing and need a serious upgrade. In some places, we don't even have encryption, and it's harming trust. It's up to the gatekeepers to keep us safe and to promote trust - we users can only do so much.

Bad SQL Writing Put to Good Use

There's a certain style of writing SQL that I really don't like. Here's an example that pulls names and addresses for people in Wisconsin and Illinois:

select p.name, a.address
from person p, person_address pa, address a
where p.person_id = pa.person_id
and pa.address_id = a.address_id
and a.state in ('WI', 'IL')
;

Basically, the author has stacked all the tables into the FROM statement, and specified how they join on the WHERE statement. This creates confusion about how the tables are intended to be joined as well as mixing actual filter criteria with the join conditions. However, it works, since the code results in inner joins between all tables, and that was okay.

My preferred style is like so:

select p.name, a.address
from person p
left join person_address pa
on p.person_id = pa.person_id
inner join address a
on pa.address_id = a.address_id
where a.state in ('WI', 'IL')
;

It's a bit more verbose, but that helps the reader. This style splits out the tables into different statements and results in clearly indicated join types and join fields. It is clear to a reader of the code the intention of joining each table. The filter criteria are located in the WHERE statement without any other statements to confuse them with. There are still cases where the result may not be as expected based on the filter criteria, but it's easier to debug.

Overall, the first example is a confusing style to use, and it can cause trouble if the joins were intended to be outer joins and were not, because the style does not have a way to specify outer joins. (LEFT JOIN is short for LEFT OUTER JOIN, which means, basically, return all records from the first table, and any data that matches in the next without missing any records from the first.)

The other day, though, I encountered a great way to use this potentially error-prone style in a way that is actually very useful.

I wanted to generate a master list of all potential ZIP codes in the US, and then filter out ones that are not in use or are otherwise invalid. I started by creating a small table with 10 rows that consist of 1 column with the numbers 0-9.  With no loop statements available in SQL, I wrote this table like so:

create temporary table num (n int);
insert into num values (0);
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
insert into num select max(n)+1 from num;
delete from num where n > 9;

That last statement is just in case I ran it too many times. I was even too lazy to write out 1-9, instead just repeating the max+1 code 9 times. It's a bit over-the-top, but it works.

To get my "master" list of ZIP codes, I joined the table to itself 5 times, one for each character in the ZIP code (because they can start with 0s, they should be treated as characters, not numbers!). Here's how:

create table master_zip as
select n1.n||n2.n||n3.n||n4.n||n5.n as zip
from num n1, num n2, num n3, num n4, num n5
order by 1
;

Simple, isn't it? Essentially, this just makes a big Cartesian product of the table with itself times 4. There's no WHERE statement, because there's no need to join on anything. (If a system required it, I would just write "where 1=1".) This generates 100,000 records. That's about 58,000 too many, according the US Postal Service, so we need to delete some of those that are not in use. But that process is for another time.

Using a style that I usually do not recommend or warn against was interesting and useful, but it requires knowing a bit more about how these things work. Had I run this last bit of code on a larger data set, it would have caused a lot of problems, like running out of disk space or RAM. So use this style sparingly and carefully!

SAS Enterprise Guide and the Log

I'm presenting on the CheckLog macro that I developed to review a log (or any file or list of files) for errors, warning, and other messages in SAS related to issues. To make sure everything is ready to go, I've been reviewing it to see if there's anything I can improve, especially in regard to SAS Enterprise Guide, the new(er) platform for using SAS.

I encountered a few issues between the two. First, the display manager statements (DM) no longer work in Enterprise Guide. The fix was easy, although a bit tricky: For Windows users (Enterprise Guide is currently only on Windows), I switched from using the DM POSTMESSAGE statement to a Windows API called MessageBox. The API allows greater flexibility than the POSTMESSAGE statement, e.g., I can set an icon to go along with the message. Now Windows users can tell what happened not only by the message, but the icon, too.

However, the DM statement that attempts to export the current log is still intact. I was unable to find a way to replace this, or much less export the Enterprise Guide log in a simple, programmatic fashion. Essentially, it is not possible to use CheckLog to review the "current log" as defined in the Base SAS sense. However, Enterprise Guide users can still check the "current log", but they have to make it an external log in one of two ways.

The first way is to export the log using PROC PRINTTO before the program executes. The code for this isn't too bad, and it can be easily added to any existing program:

/* Set a directory and filename for the log */

%let log=%sysfunc(pathname(work))\work.log;

/* Print the log to the specified location */

proc printto log="&LOG";

run;

/* Run some SAS code... */

proc sql;

create table test as

select *

from sashelp.vtable

;

quit;

/* Turn off printing to the file */

proc printto;

run;

/* Check the external log */

/* Note: The shadow is set to "N" (No) since it is not necessary to copy the log */

%checklog(&LOG, shadow=N);

This method works fairly well. However, not all users may want to use PROC PRINTTO, or they may be using it in a fashion that would conflict with the above suggestion.

The second option involves the project flow in Enterprise Guide. This solution is quite a bit more convoluted, and I outlined it, along with screenshots, on the CheckLog Enterprise Guide page. Essentially, the user has to export the log as a step in the project flow and run CheckLog in a small program. From there, the user can either programmatically determine what to do or set other steps in Enterprise Guide to run based on conditions, e.g., using the ISSUES macro variable to determine what to do next.

Overall, this is quite a bit more convoluted, and it would be great if there were some programmatic way to export the Enterprise Guide log. If anyone knows of a simple way to do that, please let me know!

SQL Remerging

One of the issues that my CheckLog macro detects is the following statement:

"The query requires remerging summary statistics back with the original data."

What's going on here? Here's an example of code that generates this statement:

proc sql;
    create table patient_bp as
    select
          a.patient_id
        , a.year
        , b.exam_date
        , avg(b.bp_systolic) as bp_systolic_avg
        , avg(b.bp_diastolic) as bp_diastolic_avg
    from patient a
    left join vitals b
    on a.patient_id=b.patient_id
    where year(b.exam_date) >= (a.year-2)
    group by a.patient_id, a.exam_date
    ;
quit;

Two tables are joined, one that appears to be at the level of patient and year and another at patient and exam date. The table seeks to find any exam date greater than two years prior to the year in the patient table. The GROUP BY statement includes patient ID and exam date. However, the variable year is specified in the select statement.

Given that an extra grouper is in the data, the above query must first calculate:

    select a.patient_id, b.exam_date, avg(b.bp_systolic), avg(b.bp_diastolic)

Since the level of data, though, includes year, it must then do the following:

    select a.patient_id, a.year, b.exam_date, bp_systolic_avg, bp_diastolic_avg

Essentially, a mini-query needs to execute to create the summary variables (averages), then another mini-query to add that summary data to the main data.

So what's the issue? This seems useful. Well, the "remerging" note can obscure data issues within the data that the coder either ignored or did not expect, since only one of these statements can be generated for a SQL query. For example, the query above actually generates duplicates at the patient, year, and exam date levels, and the remerging note hints at this issue. Normally, this does not happen with "clean" data, but we rarely have clean data. Either the patient table had more than one record per patient and year, or the vitals table had more than one record per patient and exam date, which could easily occur in our system and is valid.

In the end, there are two ways to fix this and avoid the message in the log: Either split the query, like below, or add year to the GROUP BY statement (if that is acceptable - here it is, but that's not always the case). The split query is a little longer, but I've never noticed that such queries take much longer.

proc sql;
    create table _temp_ as
    select
          a.patient_id
        , b.exam_date
        , avg(b.bp_systolic) as bp_systolic_avg
        , avg(b.bp_diastolic) as bp_diastolic_avg
    from patient a
    left join vitals b
    on a.patient_id=b.patient_id
    where year(b.exam_date) >= (a.year-2)
    group by a.patient_id, a.exam_date
    ;

    create table patient_bp as
    select
          a.patient_id
        , a.year
        , b.exam_date
        , b.bp_systolic_avg
        , b.bp_diastolic_avg
    from patient a
    left join _temp_ b
    on a.patient_id=b.patient_id
    ;

    drop table _temp_;
quit;

Note that this is essentially replicating what SQL is doing in the back-end, but since it is split out, it is obvious that the programmer knew about the potential issue and purposefully coded it that way to avoid it.

Someone suggested adding "distinct" to the select statement, but the message regarding remerging does not go away when this solution is used. Thus, any other unexpected data issues may continue to be masked by this statement, since only one can be generated for each SQL step.

Any time you're doing data transformation, it's always best to test your assumptions. A good macro for testing the level of the data (input and output) is the DupCheck macro. Adding this one line of code would have told the programmer something was amiss:

    %DupCheck(patient_bp, patient_id exam_date);

The DupCheck macro and its documentation is available online.

Good luck, and be wary of remerging!

Check the SAS Log from a Windows Context Menu

Have you ever wanted to check a SAS log from the Windows context menu? Well, now you can with a new script I developed using my CheckLog macro. Here's how:

1. Download the CheckLog SAS macro and save it to a permanent location.
2. Download the CheckLogSendTo.txt file.
3. Open the file and replace the value of the variable CheckLog with the location of the CheckLog SAS macro.
4. Save the file.
5. Change the extension of the file to "vbs".
6. Create a shortcut for the CheckLogSendTo.vbs file. Rename it as "CheckLog".
7. Copy the shortcut to the user's SendTo files here: C:\Documents and Settings\%USERNAME%\SendTo, replacing the %USERNAME% variable with the username in question.
8. Right click on a file or directory to check. Go to Send To > CheckLog.
9. Select Yes, No, or Cancel when asked whether you would like to add additional arguments.
1. Yes = Add arguments
2. No = Use the default arguments
3. Cancel = Cancel the check
10. If you said Yes above, enter the additional arguments you would like to use. See the Arguments page for more detail.

Here are some screenshots of the script in action:

Note: VBScript is required, which is unfortunately only available on Microsoft Windows. I hope to develop a platform-independent script sometime. I chose VBScript because it required no additional installation or administrator requirements on the platform I current work on.

This script should save me a lot of time! Normally I would copy the directory and filename of the log into a SAS session and paste it into a CheckLog call. But now it's more automatic, and I get the results faster.

Please feel free to send me feedback about the script!

Writing a Portable SAS Macro

One aspect of writing macros for SAS is to try to make them portable. There are two aspects of this: First, writing the process in a manner that is generalizable so that there's a need for the macro to be portable; second, writing the program so it works on multiple platforms of SAS and operating systems (OS).

The first task I've always seemed to excel at. With almost 90 general-use macros hosted on my site, I have a macro for many useful, reusable processes. I've written code with these macros where most of the code is simply calls to these macros. They save a lot of time and repetitious code.

The second task, however, has always been a bit elusive, especially since I am unable to run my macros on different OSs. A SAS license would be required on each OS to do so, even if I had UNIX, z/OS, OpenVMS, and Windows installed where I could access them. This creates some significant problems with this last task. As a result, I have come up with a few strategies to get around the issue.

The first and most powerful is to re-write any OS-specific function or process in the native SAS language. This can be tricky since a simple process like obtaining a list of files in a directory can be a simple, one-line piece of code for the OS, but several steps (and sometimes more limited functionality) for SAS.

Another step is to simply modify the macro to run several different versions of the same thing depending on the OS. SAS has two automatic macro variables that define the operating system, SYSSCP and SYSSCPL, that can be used to run conditional code that will work in the OS. This is still tricky, since I have no way to test the code to see if it works. It helps to review help documentation, examples online, and forums for those OSs, but I'm still never 100% certain that it will work as expected.

For this last part, I rely on the users out there. I received several rounds of feedback recently on my CheckLog macro, which reviews the SAS log, an external log (even non-SAS), and an entire directory of logs, with several additional features to tweak how it runs and how it notifies the user about issues. (See the documentation for more detail.) These users were able to provide specific feedback about what didn't work, and they were able to test new versions on their system quickly. Through this process, I was able to modify the macro significantly, and now it runs on all SAS-supported OSs (UNIX, z/OS, OpenVMS, and Windows). And the users have a better utility that fits their needs with insignificant cost.

Using this model for developing macros, it could be possible to add to the standard SAS library with new and very useful processes. So if you're a SAS user, join in and contribute to the community by publishing general-use macros or by testing and providing feedback to others who have done so!

What About Gas Prices?

Recently there's been a lot of hubbub about gas prices. While high, gas prices are not as high as many other things people buy, especially some things people buy on a daily basis, like coffee. For my family, I can demonstrate that the cost of gas is not as big a concern and not as big a budget item. Using Mint.com, my credit and debit cards, my wife's credit and debit cards, and many other financial accounts are linked and the transactions pooled together and categorized. Using this tool, I graphed the entire year of spending in 2010 in seconds:



Note: "Everything Else" includes Gifts and Donations, Health and Fitness, Uncategorized (cash and some checks), Travel, Business Services, Personal Care, Pets, Entertainment, and Fees and Charges. I also separated "Gas and Fuel" from "Auto and Transport" to demonstrate my point. What's left in in "Auto and Transport" includes Parking, Services and Parts, Auto Insurance, and "Other" (state transportation fees). Restaurants, coffee shops, and alcohol were included in Food and Dining, but I split those out into "Restaurants, etc.".

Our family probably does not spend like other families. We're generally fiscally conservative. We do like to buy good, healthy, and (when we can) local food, so our food prices are high. We don't splurge on electronics, entertainment, fancy phone or cable services, or personal care. We also minimize driving, which keeps auto costs lower. Both our drives to work are within 15 minutes. On the other hand we have a lot of educational debt. Even so, that's still a relatively small percentage of expenses. It should be no surprise that the top of the list is housing. That's probably true for just about anyone at any income level.

So what concerns me more? The cost of fuel or something else? Even as the cost of gas has risen, I'm not as concerned as I am about the cost of food, which is partly related to the cost of gas. However, there are more insidious factors in the cost of food: Wall Street.

As quoted by BoingBoing author Mark Frauenfelder, Frederick Kaufman wrote, in an article titled How Goldman Sachs Created the Food Crisis:

[T]he boom in new speculative opportunities in global grain, edible oil, and livestock markets has created a vicious cycle. The more the price of food commodities increases, the more money pours into the sector, and the higher prices rise. Indeed, from 2003 to 2008, the volume of index fund speculation increased by 1,900 percent. "What we are experiencing is a demand shock coming from a new category of participant in the commodities futures markets," hedge fund Michael Masters testified before Congress in the midst of the 2008 food crisis.

The result of Wall Street's venture into grain and feed and livestock has been a shock to the global food production and delivery system. Not only does the world's food supply have to contend with constricted supply and increased demand for real grain, but investment bankers have engineered an artificial upward pull on the price of grain futures. The result: Imaginary wheat dominates the price of real wheat, as speculators (traditionally one-fifth of the market) now outnumber bona-fide hedgers four-to-one.

Today, bankers and traders sit at the top of the food chain -- the carnivores of the system, devouring everyone and everything below. Near the bottom toils the farmer. For him, the rising price of grain should have been a windfall, but speculation has also created spikes in everything the farmer must buy to grow his grain -- from seed to fertilizer to diesel fuel. At the very bottom lies the consumer. The average American, who spends roughly 8 to 12 percent of her weekly paycheck on food, did not immediately feel the crunch of rising costs. But for the roughly 2-billion people across the world who spend more than 50 percent of their income on food, the effects have been staggering: 250 million people joined the ranks of the hungry in 2008, bringing the total of the world's "food insecure" to a peak of 1 billion -- a number never seen before.

As you can see in my spending graph, "Food and Dining" consumes 13% of our budget, higher than the quoted 8-12%. This category originally included restaurants, coffee shops, and alcohol, which I moved into "Restaurants, etc.", and was at 18.5% of spending. With these items removed, it seems much more reasonable, but still high. About 4 times higher than gas prices!

Given that our expenditure is already higher than the estimate in the article, and that food prices are likely to increase due to this investment scheme as well as increased transportation costs due to gas prices, I am much more concerned about the cost of food. Occasionally I joke that my son eats so much I might as well buy a farm to feed him, and that's not that far from the truth. We do partake in local farmer's markets and a community farm, but even so, those prices will probably be affected too.

So take a look at your own expenses and critically examine what's really costly. And once you figure that out, take action to reduce the costs however you can. For me, I'll chose to vote for politicians who push for financial reform and continue to buy as much local, organic food as I can.

The Powerful SAS Macro

In SAS, there are three main types of code: the DATA step, procedures (PROC), and the macro language. The macro language is incredibly powerful. It allows users to create dynamic programs, either by conditional logic, iterations, or a combination. A macro program can be as simple as running the same exact DATA step the same way every time, or as dynamic as generating custom code based on the input. (See here and here for more information on the macro language. For some examples of general-use macros, see my macro site.)

As a visual demonstration of the power of the SAS macro language, here is a comparison of SAS code and log output, printed on paper with a similar format:


SAS code (33 pages), including project-specific macros (13 pages)
(Courier New font, size 10; 1.5 line spacing; .5" margins)


SAS log from above code (391 pages)
(Courier New font, size 10; original line spacing; .5" margins)

(Note: The SAS log is a record of what SAS has done. When code is run, it documents input and output record counts, issues like warnings and errors, run time, and other information. See this page for more information.)

The code was formatted with 1.5 line spacing, although the line spacing in the log was left as-is. Additionally, the code included general-use macros (macros not specific to this project), which were not included in the printed code, but would have printed log output upon use. For example, the DUPCHECK macro, used for identifying and resolving duplicates, was used often. As one line of code, it generates, in the simplest example, 42 lines of code. (For an example of its use, see code and log.)

Even so, the ability to do so much work with so few lines of code is incredibly powerful and efficient. I am able to save time and energy, make processing more efficient and effective, simplify and standardize code, and share processes with other users. If you are a SAS user and you do not know the macro language, I encourage you to start learning!

CheckLog: A Utility for Reviewing the SAS Log

At my previous job, I worked on a SAS program that was intended to be run by someone who did not know SAS. So I needed a utility to tell the user whether the program completed successfully. The final result was a macro called CheckLog that checks a specified log, directory of logs, or the current log for error, warning, invalid, and other messages related to issues in a SAS program.

In order to accomplish this, I first needed a way to export the SAS log to a file so I could read it back in as a SAS data set and search for the terms and phrases associated with issues in the SAS program. Fortunately, there was already a utility available at work that exported the log to a file. The statement that exports the file is a simple DM statement, for example:

dm log "file 'C:\Directory\File.log' replace;" wpgm;

The next step required reading in the log into a data set. Initially, I used the import procedure (i.e., proc import) to import the text file into SAS. However, I needed to specify a delimiter that would not actually delimit the file. I wanted the file to be one large column. So I looked through the SAS font and found a character not common in English, the cedilla ( ¸ ). This character is also not usually found alone, even in languages that use it (like French). At least, I hope it isn't!

The last part is a continuing effort - identify and search for words and phrases associated with issues. The keywords were easy: error, warning, invalid, and uninitialized. The phrases, however, are elusive and open to interpretation. Sometimes phrases like "the data set has 0 observations" may be intended, and therefore should not be searched for. On the other hand, after completing an import, a data set with 0 observations is probably not intended. This type of issue requires the programmer to identify the problem.

Otherwise, the CheckLog macro can identify many common issues of varying levels of importance. Issues like the statement "numeric values have been converted to character values" or the statement "the query requires remerging summary statistics back with the original data" should be identified and resolved to avoid unintended consequences.

The macro includes several options. First, the macro can review a specified log, a directory of logs, or the current SAS log. As long as the file ends with ".log", it can be reviewed with this macro. Second, To enhance reviewing non-SAS logs, I added an argument to add additional keywords, like the word "caution" or "alert". Users could implement user-specified issues (instead of using put statements with the word ERROR or WARNING).

Third, the output can be renamed, with a default of "Log_issues". (When reviewing multiple logs from a directory, this option does not take effect, and the output is named "Logs_w_issues".) Fourth, an option to change the behavior of the pop-up message: always, never, and only when issues are found. Finally, an option to abort the currently executing SAS program if an issue is discovered. This allows the user to run code in production and end it prematurely if issues arise, so the code does not continue to execute with issues.

Readers interested in the macro can join a user group (see below) hosted by Google Groups to download the macro, view documentation, and receive emails when I updated the code. And please, send feedback! I would like to know if the macro works well and whether it checks for all of the issues users require.

---

CheckLog Site

CheckLog User Group

Description:

This group is a forum for the users of the SAS CheckLog macro to receive updates, ask questions, and post feedback. The CheckLog macro checks the SAS log(s) for issues, including lines with error, warning, invalid, or uninitialized messages. Additionally, other phrases associated with issues are checked, and specific phrases are ignored. The macro can also be used with non-SAS logs, as long as the log uses the same issue keywords.

Subscribe by Email:

Open Last Table in SAS

I've been trying to find a way to open the last table created in SAS by using a keyboard shortcut. It's taken a while and many online searches to find it, but I finally figured out a good way to do it. I created a macro and set the macro to a keyboard shortcut. Here's the entire macro:

%macro OpenTable(table=&syslast) / des="Open a table";

dm "vt %sysfunc(compress(&table))" vt;

%mend OpenTable;

Set the macro to a keyboard shortcut by typing the following code into the keyboard shortcut editor window (F9):

gsubmit '%opentable;'

By default, the macro opens the last table created, and the argument can be modified for any table.

I'm so glad I found a way to do this! This should save a lot of time!

Update:

I added more to the macro code in case the table doesn't exist. Here's the new code:

%macro OpenTable(table=&syslast) / des="Open a table";

/* Check if table exists */
%if %sysfunc(exist(&table))=1 %then %do;

dm "vt %sysfunc(compress(&table))" vt;

%end;

/* Otherwise pop-up a message that it does not exist. */
%else %do;

dm 'postmessage "The specified table does not exist."';

%end;

%mend OpenTable;

Update Update:

I've developed a rather complex macro now that does the same thing:

OpenTable.sas

Electronic Task Management

I have a lot of things to do, and sometimes I have trouble remembering it all. At home, I use my computer to do remember and remind me of many tasks. At work, I use a combination of paper and the computer. Since documents from my PC or the network are not always available, it is still useful to retain a copy of certain tasks on paper. Nonetheless, I still use a few computer tools, at home and work, to help remember all of these tasks and make task management easier.

My wife and I planned our wedding using a number of Google's online office applications, including Docs, Calendar, and Notebook. We also used RememberTheMilk (RTM), an online task management application. Managing our tasks online provided the opportunity to travel and retain access to our tasks, as well as keep an eye on tasks while at work. Gmail and Microsoft Outlook also provide some useful tools for managing tasks.

For our wedding, my wife and I kept a few documents for the wedding in Google Docs, including the guest list and a service provider comparison spreadsheet. In Google Notebook, we had song lists, service providers and costs, a long to do list, and all sorts of ideas in a shared notebook. (Unfortunately, Notebook is not longer supported, so I transferred our notes to Google Docs and have been using that application since.) For specific projects at work (for example, HEDIS), I keep a task list in an electronic document, and I usually archive the tasks as I go forward, keeping newer items at the top.

My wife and I also used RememberTheMilk for managing tasks. I used this quite a bit and shared to-do items with my wife. We went back and forth between our to-do list in Notebook, which was a long-term list, and RTM, which was a short-term list. I still use RememberTheMilk for short- and moderate-term tasks, and Google Docs for long-term tasks. RTM, Google Calendar, and Outlook's task feature are useful for scheduling a task with a reminder and a specific due date. Calendar was also useful for sharing events with my wife as well as managing our to-do list and making appointments.

I associate many tasks with emails. Using Gmail at home and Microsoft Outlook at work, I flag specific emails with varying levels of priority. Gmail has a "lab" feature that enables various flags or "stars" that users can associate with anything. I have flags for immediate tasks, "important" tasks (due soon), "assigned" tasks (due later), items to read or review, and items to monitor. Gmail also has a Task list feature. I have not used this feature extensively, since I have been using RememberTheMilk for some time, and it appears that RTM has more features (e.g., scheduling tasks, associating a task with a URL).

I hope these tips and strategies are useful to someone out there. If you have any novel and efficient ways of managing tasks, feel free to share them in a comment!

Keyboard Shortcuts

I love keyboard shortcuts. There's no better way to do things quickly than by using a keyboard. If you think about it, using all fingers instead of moving your hand/arm and clicking with one finger must be faster. But first you need to know the shortcuts. Here's a list of some good resources for keyboard shortcuts in various programs:

1. Windows XP
2. Windows Vista (Remote Desktop)
3. Firefox
4. Microsoft Excel (Others)
5. Microsoft Outlook
   
6. SAS
   

Here are some of my favorite shortcuts--ones that people don't usually know about. These are based in Microsoft Windows XP.

1. Alt+Tab for switching programs. Ctrl+Tab for switching tabs in Firefox or other tabbed programs. Alt+Page Up or Page Down is incredibly useful on remote desktops for switching between programs just like Alt+Tab.
2. Ctrl+Shift+Esc brings up Windows Task Manager. Similarly, Windows+Break brings up the System Properties. The usual Windows combinations are good, too, especially Windows+L, which locks the computer, Windows+D, which shows the desktop, and Windows+E, which opens Explorer.
3. Alt+F4 terminates a program. In some programs Ctrl+F4 terminates a window, submenu, or other child program within a program. I like to use that in SAS quite a bit. Ctrl+Shift+T opens a new tab in Firefox, and Ctrl+W closes tabs in Firefox.
4. Keyboard shortcuts can be assigned to shortcut icons. At home and work, I use the Ctrl+Alt+Shift combination for typical programs. I usually assign Ctrl+Alt+Shift+W for Microsoft Word, Ctrl+Alt+Shift+X for Excel, Ctrl+Alt+Shift+N for Notepad, and Ctrl+Alt+Shift+C for Calculator.
5. At work, I use the Ctrl+Alt combination for connecting to various programs that allow me access to something, for example, Ctrl+Alt+S for connecting to SAS, Ctrl+Alt+R for connecting to remote desktops, and Ctrl+Alt+Q for Rapid SQL.
6. In Excel, Ctrl+; enters the current date and Ctrl+: enters the current time. Ctrl+" copies the cell above to the selected cell and Ctrl+' copies a formula in the same manner. Alt+Enter inserts a line break within a cell, allowing the user to break text across more than one line. Ctrl+Home, Ctrl+End, Ctrl+Page Up, and Ctrl+Page Down are useful navigational keyboard shortcuts in Excel, too. Try them out!
   
7. In Microsoft Office, Ctrl+K creates a hyperlink on the selected text.
8. In Microsoft Outlook, Ctrl+D deletes highlighted or currently viewed mail. Ctrl+Shift+V brings up a menu to move currently highlighted/viewed mail to a folder. Alt+F4, like above, terminates the current open message (I thought Ctrl+F4 would make more sense).
   
9. In SAS, the best one to know is F9. Then you can read about all of them! Otherwise, F8 for submitting programs, F6 for viewing the log, F7 for viewing output, and F5 for viewing programs. Keep pressing F5 to toggle between multiple programs.
   
10. If the Language Bar toolbar is on in Windows, don't hit Alt+Shift. It switches the language! That one gets me all the time, since my wife uses Chinese on our computer on occasion. And for some reason I bump it quite often.
    

That's about all I can think of right now. I like learning new keyboard shortcuts, and I know there are a lot more to learn. What are your favorites?

My Home Computer Desk

I have a bit of an unusual work space for my computer at home. On the right of the keyboard, I have a trackball mouse. On the left, I have a regular mouse that is not used for moving the cursor. I also have a wireless mouse on the monitor area of the desk and a video game controller. Here's a photo:

The trackball mouse aids with avoiding carpal tunnel syndrome and it saves space. The small left button is set to the middle button and the small right button is set to "Back" for internet and file browsing. The mouse on the left side serves two purposes: First, this style of mouse has a better scroll device than the trackball mouse, which doesn't really have one. Second, if it's ever necessary to get the precision of a regular mouse (editing images, for example), then it is available for use. The laser is currently covered with a small sticky pad to avoid any cursor movement from it. The wireless mouse is used for viewing the computer desktop on the TV, away from the computer monitor. It's great when watching internet TV shows on an actual TV (hulu.com is great for internet TV).

Also note the USB extension cord, which is useful for any other peripheral devices one would need to plug in temporarily (a camera, for example), and the wireless headphones, which allow for freedom of movement and less cord clutter. The small device under the monitor is a CRYPTOCard, used for accessing remote computers at work. The monitor is on a base that rotates and adjusts vertically, making it perfect for getting just the right position for the user. The combination of the trackball mouse and vertically adjustable monitor have significantly improved the ergonomics of my computer desk, making me a lot more comfortable, especially during long hours of work (or play!).

Here's an older version of the setup with an older left mouse and keyboard and without the wireless mouse.

I (thankfully) upgraded the monitor from the bulky, unadjustable CRT monitor. Since I spilled juice on the keyboard (not recommended!) I had to get a new one of those too, although I went with a cheap keyboard. Actually, the only component I would change about the current setup is the keyboard, which isn't exactly comfortable and the extra buttons on the left and right sides are not prominent enough to be very useful. I liked my old one, but I spilled juice on it...

By the way, all of these devices except the keyboard are from Logitech. I really like their computer input devices. The software isn't completely bug free, but once I got the settings where I like them everything works just fine. I haven't used the wireless mouse as much, but I advise getting a USB extension cord with it, as its range is limited.