Recipe Database

I've been working on a recipe database for my wife, to help her plan meals by being able to easily peruse recipes (especially favorite recipes) and associate those recipes with days and meals. I also wanted to be able to easily add recipes, ingredients, and grocery items. Here is the most recent relationship view of the database:



Each recipe is associated with a list of directions and a list of ingredients. Since ingredients are variations on specific items, they are not distinct grocery list items. For example, one recipe may call for 2 tomatoes and another may call for 3 tomatoes, and each would be a separate ingredient, but only one grocery item with a combined total of 5 units. Grocery items also have alternatives in a self-referential link (ALT_GROC_ID), which would be appropriate for recipes with "or"s in the ingredient list or legitimate alternatives (e.g., sea salt for salt).

Then there are the weekly plans, called "Lists". I realized this term may cause some confusion between grocery lists and plans, so I may change it in the future. For now, I'm focusing on completing the functionality of the database and not improvements to the delivery. Each list is associated with a beginning and end date ("from" and "thru" on the Lists table). Each list has several detail lines in the Lists_detail table that associates days with meals for the days between the beginning and end dates, inclusively. Each meal is then related to a recipe.

Currently the database exists in a Microsoft Access database, and eventually I would like to port it over to SQLite or something similar, with HTML forms. For now, there are several Access forms for data entry, and the initial "Start" form that opens with the database provides access to many of those forms.



The top section of buttons appends new plans to the Lists table with different start dates. For a typical week, a user might start planning on Saturday for the next week Sunday - Saturday and the following week Sunday - Saturday, and would thus start two new lists using the Tomorrow and "Week from Tomorrow" buttons. The two new lists would be associated with today's date, which is displayed in the upper right hand corner.

Once the new lists are initiated, the user can open them by using the "Today's Plans" button in the "Open..." section. The user can also open recipes for viewing and the grocery list based on today's plans. Eventually I'm going to switch the grocery list from a view-only report to a form based on an append query, which would keep track of the lists through time and build an inventory of the kitchen. (This is where the confusion with "lists" would come in to play.) Also from the start form, the user can also add or edit recipes, add or edit grocery items, or delete plans.

The main function of the database is to help the user plan for a week or two of meals by associating days and meals with recipes and ultimately generating a list of grocery items to use while shopping. Here is the plan entry form (click for a larger view) that opens when the "Today's Plans" button is used:



The list date range is the first section, with arrows allowing the user to switch lists. The navigable lists depends on how the user opened the form based on the buttons in the start form. The user then uses the drop-down boxes to select days and meals. Next, the user can use the bottom part of the form to look up recipes. Say the user wants a rice dish, so the user clicks on the binoculars and searches the Name field for "rice". Currently there are fields for descriptions, authors, origins, and URLs for recipes, which could also be added to the form to help the user find a particular recipe.

The ingredients and directions are listed to help the user figure out what would be appropriate to prepare for any given day's plan. Once the recipe is selected, the user enters the recipe ID next to the meal. Alternatively, the user can enter something other than a recipe for a meal, for example, "go out to eat with friends" or "order pizza". Once the weekly plan(s) are complete, the user can return to the start form and open the grocery list report, which is organized by the location the items are found in the store, like produce, meats, aisles, etc.:



There is a lot of work still to do. As I mentioned, I have to figure out how to append the grocery lists to a list table to maintain a history. Then, I need to enter a lot of recipes. The recipes in the database currently were entered using a SAS program and borrowing work's SAS server to process it. The next recipes will be entered using the new form as a test run of how well it works. I expect I will revise the form quite a bit to make it flow better. I also have to figure out how to merge disparate measurements, e.g., cups and ounces, and only display a total quantity on the grocery list. Some merges will not be possible, as the above image shows for kosher salt: 7.5 tsp and "to taste". (Note the odd "40 taste" - this is a conversion of the ingredient "8 dashes of hot sauce", which is unquantifiable, and the dummy records I entered had 5 of the same recipe, thus "40 taste".) Then there's the conversion into a "better" database tool and using web forms instead of these proprietary-format forms.

For now, though, I am very happy that the database, although not fully populated with recipes, is usable and can save the user (just my wife, for now) a lot of time.

A Room

A Room with a Nu

A Room with a Gnu

Original room image credit jaybergesen (source).

SAS Macros versus Python Functions, Part 1

I recently started reading Dive Into Python, a primer to the programming language Python (which I converted to MOBI and loaded onto my Kindle - available here), and I was delightfully surprised how similar functions are to the SAS macro. Here are some examples:

First, the very first line of a function or macro, that defines it as a function/macro, is very similar. In Python, one would write:

def buildConnectionString(params):

In SAS, it is very similar:

%macro buildConnectionString(params);

The terms def and %macro begin the code by stating what follow: a function definition or a macro. The "buildConnectionString" is the name of the function/macro, and enclosed in parentheses are the function/macro parameters or arguments, separated by commas.

Second, it is often a good idea to have some documentation on the function/macro. In Python, the documentation is stored in a doc string; in SAS, a very short and not as useful description string is available:

def buildConnectionString(params):
    """Build a connection string from a dictionary of parameters.

    Returns a string."""

SAS:

%macro buildConnectionString(params) / des="Build connection string. Returns string.";

UPDATE: I'm not sure where I learned the 40-character limit mentioned below, but I just read in the SAS help documentation that the limit is 256 characters. Nonetheless, reading the description is still limited by screen space, so the alternatives I mentioned here are still very useful.

Unfortunately, SAS macros are limited to 40 256 characters in the description. This can be worked around by offering HELP as a valid value to the first argument, which then outputs additional information about the macro. My macros are documented in a spreadsheet, and I have a macro (ArgList) that reads in the spreadsheet and outputs additional details about the arguments when I need it. One day, I would like to convert all of my macros to use a standard header that contains the information about the arguments, its purpose, and other details in a standard way so I can modify the ArgList macro to read the macro itself instead of external documentation. In Python, though, the doc string can be viewed when referencing the function, on the fly. That's incredibly useful, and much more flexible than SAS.

One area of difference is the syntax and the meaning of space: In all Python code, space has meaning, whereas in SAS code in general, it does not. Even so, I typically indent my SAS code just like Python code requires (in most cases - I try to maintain readability, too).

Once I learn more about Python, I'll post more comparisons!

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!

Kindle Review from a Newbie

I received a Kindle for my birthday last month, and ever since, I have had it with me quite a bit. I've read a number of books, online articles, downloaded articles/books (hello Gutenberg project!), personal documents, PDFs, and alternate ebook formats (e.g., text, Mobi). I've also viewed images, listened to music, played games, and listened to articles with the text-to-speech feature.

My first impression? AWESOME. This is one of the most useful devices I own, and it has helped me face a mounting task: to read that which I "should" read, including articles for work, research articles, random pages online, Wikipedia entries, classics I should have read in school, and simply books I've been meaning to read for a long time.

Further, the device is extremely useful for work. I don't have to print out long documents for meetings: I simply load them on to the Kindle. (I don't have the 3G version, which is fine by me. I don't want to pay extra fees for content delivery, and the USB cable is easy enough to tote to work where there's no WiFi.) I maintain a project list as a text file with current items on the first page and completed items on the second page. Once it fills up I plan to archive it for review purposes.

The capability to search, easily navigate a document, and have a document anywhere has been immensely useful, especially during critical moments in a meeting when someone says, "I think that's documented in that XYZ document that I've either printed out a 150-page version of and will take me 15 minutes to find or I did not print it or I left it at my desk, so none of us will know the answer until later." Just yesterday we were fumbling around trying to figure out the next date we should meet, when we should run a process, and when we should communicate by email or phone. I opened up a PDF calendar I loaded and it made planning much easier.

Of course, this could be done with just about any e-reader, right? Yes, but the Kindle has that iPod-like cool and ease-of-use. The ease-of-use is really the big advantage. The Kindle loads quickly, turns pages quickly (except in some PDFs, especially those with too many images), has an excellent organization method ("collections", where a book can be located in multiple collections), a "newspaper" feature with auto-archiving of old "editions", and all the other features I mentioned above. It's also comfortable to hold and it has the potential for more capabilities. For example, it has a microphone with no known current use, but I imagine that voice commands (e.g., "turn page") would be an excellent addition, especially for people with disabilities.

Even though people complain that it has a locked-down ebook format, so do the other e-readers, especially the Nook, which claims it doesn't. Barnes and Noble use a more open standard, but they still lock it down with DRM, making it practically inaccessible to other ereaders. And frankly, there are ways around these limitations, as any savvy computer geek can find a way to break DRM. (Hopefully, those people are being honest and not selling or distributing copies.)

Have I had any hiccups? Yes. First and foremost: DO NOT BUY THE NON-LIGHTED COVER BY AMAZON!!! Due to its construction, it causes the Kindle to short and restart, seemingly at random. It is not the Kindle. It is the cover! The lighted cover is fine, in fact, I exchanged the non-lighted cover for the lighted cover, and I haven't had any problems with it restarting since. (Caution: The lighted cover costs a bit more, almost half of the cost of the Kindle!)

Additionally, I've had some odd problems with PDFs. Sometimes viewing PDFs with a lot of images makes the page turns very slow. PDFs maintain the format, so they are often hard to read in the default orientation. Rotating the orientation to landscape helps with that problem. It's possible to load images onto the Kindle, which is a hidden, experimental feature. The images look similar to the screen savers, and are a bit slow to navigate as well. Simply put, it's not a device built for handling images (yet). So really not too many drawbacks directly related to the Kindle!

Overall, it's a great device. Easy to use, flexible, and quite frankly, beautiful. My wife says, "It's not beautiful - it's sexy." Please note: she said that, not me.