12 Nov

Weight Loss (Not What You Would Expect)

Part of the reason I left my last job was that I’d gained a crazy amount of weight over a year, 20 pounds.  I figured, if nothing else, a new job would at least lessen the things I was stressing over.  Well, so far, so good, I’ve gotten 10 pounds of it back in just over 6 weeks.  But that isn’t the interesting thing.

What is interesting, is how the weight loss has worked relative to my exercise, and more specifically to the type of exercise.

To keep this short, I track everything I eat and I assign calories to the exercises I do.  So, I have a good estimate of what I’m doing in my workouts and what I’m eating.  I also track my weight, everyday, so I can see my daily weight change.  In theory, if I go negative 700 calories, I should lose .2 pounds.  I know my break-even from my history but that is a post for another day.

What I found is that not all exercise is created equal.

I’m doing 3 types of workouts: rowing machine, biking and hiking.  If anyone but bot-spam ever comments, I’m not anti-weights, in fact I recommend them, it’s just that my body is pretty beat up and I needed a different approach.  Anyways, this is what I’ve found looking at my exercise patterns over the last couple of months and my weight gain/loss the next day:

Exercise # of Times Net Gain/Loss (Next Day)
Biking 14 +3 Pounds
Hiking 8 -3.6 Pounds
Rowing 30 -4.4 Pounds
Biking/Rowing (Morning/Evening) 11 +3.2 Pounds
Rowing 19 -7.6 Pounds

To be honest, it’s hard to know anything from this.  The sample size is small.  I’m in an easy weight loss phase, where I actually had to work (if eating crap is work)  to maintain the weight, maybe weighing myself from day-to-day is really just measuring water-retention or how much food I ate the prior day?

But I can tell you that this data fits to my anecdotal experience with weight loss: biking, while something I like, something that gets me outside, that I feel better doing, has, for me, not been a very effective weight loss tool.  I’ve done a lot of 2+ hour bike rides and not lost weight.  I always suspected that was due to overeating after the ride but I know that isn’t the case in these numbers.  Heck, I’m gaining weight after a bike ride.

One other factor in play here is intensity.  When I bike, especially when I do a 2-a-day (biking/rowing), my bike ride is a pretty tame 14 mile ride on mostly flat ground.  Unless I run into a windy morning, I’m not working that hard.  Rowing for 30+ minutes is never not hard and the hikes I do, are reasonably hard too.

carbon comp

Tell me this won’t make me fat!

Or, it could be that doing 2-a-days is over-training and that is part of what is limiting the weight loss?  I might be better off doing less as my data suggests that working out more does not equal more weight loss.  This is also something I’ve observed over the years.

I may try going in a different direction for a few 2-a-days and see what happens.  I can shorten my ride by +/- 15 minutes and make it harder by doing this.  Increased intensity may be the key?  The problem with this is that it’ll make it very hard to row as that hits the legs hard too.  But, I can give it a try and see what happens and if it doesn’t work, well, then I can try limiting my weekends to a single workout per day.

13 Jun

Trabing Fire

Some photos I found from the Trabing Fire taken in June 2008.   I was digging around, found them, and thought they might be worth a post, especially since I haven’t written anything in coming up on 2 years.

11 Sep

SQL Server: Parsing A String Into Rows

I haven’t written a SQL article in, well, quite awhile, and I guess this won’t really count as one either.  Anyways, I tossed this up over on the SQL Team forum and I liked it so I’m putting it here.

The solution relied on a couple of arcane concepts and functions.  First, I used PARSENAME which parses a string separated by periods.  It’s basically the same idea as SUBSTRING_INDEX in MySQL only a bit less flexible because of the period.

I’ve talked about walking the tree before but, in essence, it breaks down a delimited string by joining a table of numbers and using that sequence to get, say, the third delimited value.

You have the following string.


The first part is the ORDER_ID and the rest of the string is the SKU’s attached to that order.  Yeah, it’s a bit weird but you see things like this in converted older system or in some really ad hoc reporting tools.

Now, we want to convert that string as follows:

Order_ID	SKU
12345678 	SRPPR             
12345678 	SCNRQ
12345678 	SCINC

Obviously, that isn’t easy but here’s one way to do it.

DECLARE @var char(30);
DECLARE @var1 char(30);
SET @var = '12345678 SCINC, SCNRQ, SRPPR';
SET @var1 = REPLACE(REPLACE(@var,SUBSTRING(@var,1,CHARINDEX(' ',@var)),''),', ','.')

SELECT SUBSTRING(@var,1,CHARINDEX(' ',@var)-1) AS Order_ID,
PARSENAME(@var1,zombie) AS SKU
FROM walkers
WHERE LEN(@var) - LEN(REPLACE(@var,' ','')) >= zombie

First, I created a walkers table as follows:

CREATE TABLE walkers (zombie int);

Which I populated with a running sequence of numbers starting at 1.

SELECT * FROM walkers


Here’s what it does. @var1 does two things: it converts the comma/space to a period so PARSENAME can use it and it also removes the ORDER_ID field and using the variable makes it easier to explain. This is what it does:

SELECT REPLACE(REPLACE(@var,SUBSTRING(@var,1,CHARINDEX(' ',@var)),''),', ','.')


This code:

SUBSTRING(@var,1,CHARINDEX(' ',@var)-1) AS Order_ID

is a straight-forward grab of the order id from the string. Basically everything from the first space backwards.

Note: My original post or the forum didn’t include the -1.

Finally, this code

PARSENAME(@var1,zombie) AS SKU

uses the PARSENAME function and the zombie field from the walkers table.

This might make more sense if we change the code slightly to include the zombies field.

SELECT SUBSTRING(@var,1,CHARINDEX(' ',@var)-1) AS Order_ID,
PARSENAME(@var1,zombie) AS SKU, zombie
FROM walkers
WHERE LEN(@var) - LEN(REPLACE(@var,' ','')) >= zombie

Order_ID	SKU	zombie
12345678	SRPPR   1
12345678	SCNRQ	2
12345678	SCINC	3

Finally, this was for a single record. If we had full order entry system you would simply join the walkers table to the order data and limit the results to the number SKU’s for each record. My code does this but since it’s only for a single record it might seem confusing. Anyways, a full outer join or a non equi join would fit the bill.

I hope this isn’t too confusing. I did it on the fly and didn’t sequence it as well as I have in most of my examples. Honestly, I just liked the solution and wanted to add it here. Too often, I think, you see people run to a function to solve this kind of problem, especially on the SQL Server side of things when there are other ways to solve the problem.

14 Aug


I haven’t written anything in a couple of months, slamming post after post after post, will do that to you.  But, since I ran into an old problem, SSRS glomming onto port 80, I figured it was time to add a post.

So, here’s the deal.  I have a small web crawler I made using the LAMP stack.  It’s simple and based on a book I bought for $0.1 + $3.99 shipping (yes, Amazon still knows how to get around paying taxes).  Anyways, I did a reinstall of SQL Server 2012 and it broke WAMP via port 80.

The standard answer if you go to the WAMP forums is to set SSRS to a manual start up, then if you need it, you just turn it on.  I don’t find this to be a good solution because by the time I start messing around with it again, I’ll forget that.  I’d rather reset the port.

The best answer I found, or rather Google found, is the following link.


It’s very clean and the only thing I added to it was a restart of SSRS.

Since the last time I did this I set SSRS to manual mode, and I wasn’t stressing on it, I give myself a 50/50 chance of having broken something that I’ll later need, but whatever, I’ll deal with that when it comes up, if it comes up.

05 Jun

MySQL: Cheesy Way To Row Number

The classic way to create row numbers in MySQL is to use a variable and increment it for each row.  I did an example earlier showing it this way.  In SQL Server, you just use ROW_NUMBER() or one of its variants.  But, just for fun here is another way which was in the SQL Cookbook.

For this I’ll be using my good old military spending data.

mysql> select @ctr := @ctr + 1 AS rowCtr, region, country, spending
    -> FROM militaryspending
    -> JOIN (SELECT @ctr := 0) AS a
    -> ORDER BY spending DESC LIMIT 5;
| rowCtr | region        | country        | spending |
|      1 | North America | United States  |   711421 |
|      2 | Asia          | China          |   142859 |
|      3 | Asia          | Russia         |    71853 |
|      4 | Europe        | United Kingdom |    62685 |
|      5 | Europe        | France         |    62535 |
5 rows in set (0.00 sec)

Anyways, that is pretty easy. Suppose, however, that we want to see the last 5. We can do this, but it won’t number the way we, or I, want it to.

mysql> select @ctr := @ctr + 1 AS rowCtr, region, country, spending
    -> FROM militaryspending
    -> JOIN (SELECT @ctr := 0) AS a
    -> ORDER BY spending LIMIT 5;
| rowCtr | region        | country    | spending |
|      1 | Africa        | Seychelles |      9.3 |
|      2 | Africa        | Cape Verde |      9.7 |
|      3 | Africa        | Mauritius  |     10.1 |
|      4 | North America | Belize     |     15.7 |
|      5 | Europe        | Moldova    |     20.8 |
5 rows in set (0.00 sec)

I, actually, wanted to see it numbered as 126, 125, 124, etc.

That’s actually easy to do, just do a subquery / inline view as follows:

mysql> SELECT rowCtr, region, country, spending
    -> FROM
    -> (
    -> select @ctr := @ctr + 1 AS rowCtr, region, country, spending
    -> FROM militaryspending
    -> JOIN (SELECT @ctr := 0) AS a
    -> ORDER BY spending DESC
    -> ) AS b
    -> ORDER BY rowCtr DESC LIMIT 5;
| rowCtr | region        | country    | spending |
|    126 | Africa        | Seychelles |      9.3 |
|    125 | Africa        | Cape Verde |      9.7 |
|    124 | Africa        | Mauritius  |     10.1 |
|    123 | North America | Belize     |     15.7 |
|    122 | Europe        | Moldova    |     20.8 |
5 rows in set (0.00 sec)

The SQL Cookbook also had an interesting approach, they used a COUNT(*). It’s a weird solution because it can only work on an ordered list of some sort. For example, if we alphabetized our list of countries it would work as follows:

mysql> SELECT a.country,
    -> a.spending,
    -> (SELECT COUNT(*) FROM militaryspending WHERE country <= a.country) as rowCtr     
    -> FROM militaryspending a
    -> ORDER BY country LIMIT 5;
| country     | spending | rowCtr |
| Afghanistan |      878 |      1 |
| Albania     |      197 |      2 |
| Algeria     |     8665 |      3 |
| Angola      |     3647 |      4 |
| Argentina   |     3295 |      5 |
5 rows in set (0.01 sec)

I dunno, weird solution but it’s what he did, and it does work in some situations. I read that section really quick so maybe I missed something, something really obvious but I’ll stick to the variable for now.

04 Jun

SAS: Macro Variables

I’m going to commit the cardinal sin and admit that my heart isn’t hugely into writing this.  I’m plowing the SAS Advanced Study Guide, I’m at page 300, and I’m exactly a third of the way through it, and that is the easy part.  And the truth, the hard truth, is that I should be plowing SSRS and SSAS over this.  But, I’m in, and so we continue the process.

Macro variables allow you to dynamically write code in your programs.  For instance, you can use them to define a WHERE clause in a PROC SQL statement or even define the name of a data set.  SAS likes to demo them for writing footers and titles.

There are two kinds of macro variables: automatic and user-defined.  Automatic are system defined and do things like return the date, time, SAS release or other system information.

In order to use a macro variable you define it with a %LET statement and then you use it by preceding it with a & symbol (this is actually more involved, but that’s in another article).

  • They are character data, always.
  • Because they are characters, expressions, too, are characters, and, as such, are not evaluated.
  • Case is retained.
  • Quotation marks are retained.
  • Leading and trailing blanks are removed.

So, when you defined a macro variable, these are all perfectly acceptable.

LET %myvar = 123; /* 123 */
LET %myvar = abraham lincoln; /* abraham lincoln */
LET %myvar = Abraham Lincoln; /* Abraham Lincoln */
LET %myvar = Abraham's horse; /* Abraham's horse */
LET %myvar = Abraham + Horse * Cow; /* Abraham + Horse * Cow */
LET %myvar = 1 + 2 + 3 / 4; /* 1 + 2 + 3 / 4*/

You can also define a macro variable with a macro variable.

LET %var = "My Variable"; /* "My Variable" */
LET %var1 = &var; /* "My Variable" */

LET %var = var1; /* var1 */
LET &var = Billy Bob; /* var1 will equal Billy Bob */

So, you can not only assign a variable the contents in a variable, you can create a variable based on the value in a variable.

Here is a relatively simple example (that somehow vanished from WordPress) of macro variables.

%LET mysex = F;

proc sql outobs=5;
SELECT name, age, sex, weight, "&sysdate"
FROM mysql.admit
WHERE sex = '&mysex";

Name                 Age  Sex    Weight  
Almers, C             34  F         152  20APR13
Bonaventure, T        31  F         123  20APR13
Johnson, R            43  F         137  20APR13
Reberson, P           32  F         151  20APR13
Eberhardt, S          49  F         172  20APR13

Here is what the code does. It creates a macro variable name mysex and sets its values to F. In the SELECT statement, the automatic variable &sysdate is used to generate the column with all the 20APR13 values. Then, finally, the WHERE clause is set to WHERE sex = F.

One thing to note, the examples in the book all show a single-quote, macro variable, double-quote in the code samples. Neither that, or single-quotes worked for me. I had to use double-quotes to get macro variables to work. I can’t guarantee that this is correct. It might be some quirk of where I’m writing this code that I don’t know about. Anyways, my examples will all use double-quotes.

I’m going to stop at this point. The second half of this second lists a number of options and they make a good post by themselves. Fortunately, most of them should be familiar from SAS BASE.

At page 315, only 591 more to go. Sheesh!

Oh, and the best part of this post, WordPress ate my sample code the first time I posted it.  Yee haa!

03 Jun

SAS: Last Post For A While

This will, probably, be the last SAS post, for a bit, maybe a long bit.  I’m going to back away from the SAS certification.

I’m doing this for the following reasons:

  1. I don’t see a lot of SAS programming in the Bay Area.  It’s out there, even here, but there isn’t a lot.
  2. The work I do see often comes with the caveats of wanting more SAS experience than I’m going to have, and, they often want experience in an industry.  The industry is almost always health care  / clinical trials.
  3. The SAS jobs almost always, at least around here, come attached with a statistics requirement.  I could probably get around that but it would force me into a direction that I’m not quite ready to pursue.

So, I’m going to keep SAS on the back burner, maybe finish it off in the future, but for now, it’ll be something else.

So, if by some chance any of this was helping someone learn SAS, I wish you luck, but for now I’ve going to redirect my efforts.

03 Jun

SAS: PROC SQL, A Few Options

PROC SQL has a handful of options which apparently we should know so I’m going to roll through them.


These limit your source data or your output.  One works when reading data and the other works when outputting data.  I’ll let you guess which one is which.  You use them as follows:

proc sql inobs=5 outobs=10;

ID    Name            Sex       Age      Date    Height    Weight  Level      Fee
2458  Murray, W       M          27         1        72       168  HIGH     85.20
2462  Almers, C       F          34         3        66       152  HIGH    124.80
2501  Bonaventure, T  F          31        17        61       123  LOW     149.75
2523  Johnson, R      F          43        31        63       137  MOD     149.75
2539  LaMance, K      M          51         4        71       158  LOW     124.80

This is another of those award-winning examples that I give. This query would be the same, regardless of which one I set, however, it is useful, I think, to show that both can be used in a PROC SQL statement. This is, exactly, the kind of thing SAS will test for.

Here’s a more interesting example:

proc sql inobs=3 outobs=4;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'M'
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex='F';

 Name                 Age  Sex
 Almers, C             34  F  
 Bonaventure, T        31  F  
 Johnson, R            43  F  
 Jones, M              29  M

What happened is that SAS read 3 rows from the two union statements, totaling 6 records, however because the output was limited to only 4 records that’s all you get. Just to prove that I’ll change the outobs to 6.

proc sql inobs=3 outobs=6;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'M'
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex='F';

Name                 Age  Sex
Almers, C             34  F  
Bonaventure, T        31  F  
Johnson, R            43  F  
Jones, M              29  M  
LaMance, K            51  M  
Murray, W             27  M


I'm looking for the code, that should be here, but isn't, because SAS flaked.

I’m looking for the code, that should be here, but isn’t.

If you add the PROMPT option to a PROC SQL statement, it should, because it doesn’t work for me, prompt you to verify that you are limiting the records. In other words, it will allow you to bypass the limits you specify.  The default, which worked really well on my system is NOPROMPT.


All this does is fluff up your output by double-spacing it.  The default is NODOUBLE and it only works in LIST output.


This is another of those exciting options.  It adds row numbers to your queries.  The default, is NONUMBER and just because I haven’t done an example in 100 words or so, here you go. Oh, and I gave you bonus content, free, see how generous I am, and added a DOUBLE to it.

proc sql outobs=5 double number;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'M';

   Row  Name                 Age  Sex
     1  Murray, W             27  M  

     2  LaMance, K            51  M  

     3  Jones, M              29  M  

     4  King, E               35  M  

     5  Pitts, D              34  M


The default is NOSTIMER.  All this does is write query processing time to the log. It must be enabled as a system option.

It looks something like this,

NOTE: SQL Statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds

I tried, but was unable to, turn it off.


FLOW is another one of those, or these, things that impact only list output. Here’s to the good examples that I’m not going to be able to provide.

Let’s look at each option.

NOFLOW, which is the default, means that if you generate a result, that is longer than the page width, that it overflows into the next line.

FLOW = x, means that a columns maximum width is x. In other words, if x=15, that a column will wrap when 15 characters are in a column.

FLOW = x y, means that a column will have a width between x and y. SAS, specifically uses the word, BETWEEN, so I don’t know if they really mean inclusive of x and y, or greater than and less than. For example, FLOW = 10, 15 means that a columns length will be between 10 and 15 characters.

FLOW, means, FLOW = 12 200.

Essentially FLOW is telling SAS to not flow into the next line, and what to do when that might happen.

I really need to get some samples for this but I probably won’t.

You can reset options by using the RESET command.  It works as follows:

proc sql double outobs=5;
	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'F';

	reset outobs= nodouble;

	SELECT name, age, sex
	FROM mysql.admit
	WHERE sex = 'F';

Name                 Age  Sex
Almers, C             34  F  

Bonaventure, T        31  F  

Johnson, R            43  F  

Reberson, P           32  F  

Eberhardt, S          49  F  

Name                 Age  Sex
Almers, C             34  F  
Bonaventure, T        31  F  
Johnson, R            43  F  
Reberson, P           32  F  
Eberhardt, S          49  F  
Nunnelly, A           44  F  
Oberon, M             28  F  
Quigley, M            40  F  
Takahashi, Y          43  F  
Ivan, H               22  F  
Wilcox, E             41  F

I almost skipped this one but I’m glad I didn’t. I never would have expected that you reset the outobs as outobs=. Of course, if you wanted to you could simply invoke a second PROC SQL statement, with the options you wish, but RESET allows you to do it within a single PROC SQL, which probably makes things run a bit faster and keeps the code a bit cleaner.

Finally, you can view “system” option settings with the PROC OPTIONS statement. Here’s a quick example. Note the word system.

proc options option=number value;

In the log,

Option Value Information For SAS Option NUMBER
    Option Value: NUMBER
    Option Scope: Default 
    How option value set:  Shipped Default

If you don’t include the values keyword this is all you get,

proc options option=number;

 NUMBER            Print page number on each page of SAS output

I think two things. One, this post went longer than I had planned. I say that because I know anyone reading something like this wants to hear me bitch about the writing I’m doing. Two, I think I’m just about done with PROC SQL.

02 Jun

SAS: PROC SQL, Dictionary Tables And sashelp

There are, a lot, of dictionary tables. Here’s three queries that list a few of them.

proc sql;
	FROM dictionary.options;
	FROM dictionary.tables;
	FROM dictionary.extfiles;

You don’t want to see the results, it’s really long, but the queries would list the current system options, table list and the currently assigned filerefs. You can see what the tables look like with the following queries

DESCRIBE TABLE dictionary.options;

   optname char(32) label='Option Name',
   opttype char(8) label='Option type',
   setting char(1024) label='Option Setting',
   optdesc char(160) label='Option Description',
   level char(8) label='Option Location',
   group char(32) label='Option Group'

You can also access the same information with the following code.

proc print data=sashelp.voption;

Note that this one is voption, because it’s a view, while the other variant is options. +1 for SAS consistency with the “s”. The result is also really long so I’ll spare you that one also.

The link below has more information.


01 Jun

SAS: Views

This post will be similar to the earlier one on indexes.  Views look fairly clean in SAS and, for the most part, I think I know what is going on here, so I’m only going to hit parts where I feel some doubt, however small.  As such, this won’t be the most useful post for someone, who isn’t me, to read.

Creating a view in SAS is pretty much like every other view you’ve created.

libname mysql '....';

ods listing;

proc sql;
	SELECT name, age, sex, height, weight
        FROM mysql.admit;

proc sql outobs=5;
SELECT * FROM myview;

Name                 Age  Sex    Height    Weight
Murray, W             27  M          72       168
Almers, C             34  F          66       152
Bonaventure, T        31  F          61       123
Johnson, R            43  F          63       137
LaMance, K            51  M          71       158

You can display the definition of a view by using DESCRIBE VIEW which will write the view’s definition to the log.

proc sql;

28         DESCRIBE VIEW myview;
NOTE: SQL view WORK.MYVIEW is defined as:

        select name, age, sex, height, weight
          from MYSQL.ADMIT;

SAS offers up a handful of suggestions on how, and when to use a view.

  • They recommend against using an ORDER BY (you can do it) because other users of the view may want to sort it differently.
  • If the data is reused, a lot, they recommend creating a table instead of a view.
  • If the table structure changes a view can become unusable.

You may have noticed that I did not use a LIBREF in the view examples I created earlier.  SAS, actually, recommends that you do that if the view resides in the same resides in the same library as the table.  In my case, they’re both in WORK.

You can assign a LIBREF in the view and not use a separate LIBREF statement.  For example:

proc sql;
	SELECT name, age, sex, height, weight
    FROM mysql.admit
	USING LIBNAME mysql '....';

I think just knowing that, that can be done, would be enough for the test.

You can, just like in a regular database, UPDATE, DELETE or INSERT into a view, in the right situations. Per SAS,

  • You can only update a single table in a view.  You cannot update a query that has a join or a subquery.
  • You cannot update a derived column.
  • You can update a view with a WHERE clause.  The book doesn’t state this but I would assume that inserts would also be restricted by the where clause, which will act like a constraint in a view.
  • You cannot update a view that has a HAVING, ORDER BY or GROUP BY clause.

You get rid of a view the old-fashioned way.

25         DROP VIEW myview;
NOTE: View WORK.MYVIEW has been dropped.

One last note, you cannot join a view.

I’m kind of scared when it comes to views because the book’s chapter on them is really short. I’m afraid that I’m missing things. At the same time, this book has a different feel to it compared to the SAS Base exam. Base felt really petty, even in the book’s sample questions, but I get the sense that the scope here is large enough that they “may” not hammer as much minutiae on this exam.

I guess I’ll have to take it to find out.