Tuesday, November 25, 2014

SAS: Enterprise Guide AUTOEXEC

Wthin Enterprise Guide, if you name a Process Flow AUTOEXEC, EG will prompt you to run that process when you open the project.  Note you need to name the Process, not the Program.



Saturday, November 22, 2014

Linux: Run a program every hour

Saw this here (http://linux.die.net/Linux-CLI/scheduling.html):

You can also use the -f option to have at execute a particular file (a shell script).


at -f shell_script now + 1 hour

This would run the shell script 1 hour from now.



So I tested it as follows.  First, here is SAS program test_hourly.sas:

filename outbox email 'bqualls@firstanalytics.com';

data _null_;

file outbox
    to=('bqualls@firstanalytics.com')
    subject="Test hourly process"
    ;

put "This is a test run at &SYSDATE9 at &SYSTIME..";
put "This program will run again one hour from now.";
put " ";
put " ";
put " ";
put "(signed)";
put "The First Analytics Team";
run;



And here is the script:

#!/bin/bash
#schedule this same script to run again one hour from now
echo "sh test_hourly.sh" | at now + 1hr
#run sas program
sh runsas.sh test_hourly



And I started the script for this first time like this:

echo "sh test_hourly.sh" | at 5:30pm November 22



Note: must cd to the directory where test_hourly.sh exists before issuing the above instruction.



Thursday, November 13, 2014

Excel: #VALUE error when using SUMPRODUCT and INDIRECT

This is a weird one...

This gives #VALUE error:

     =SUMPRODUCT(INDIRECT("CP!"&ROW()&":"&ROW()),INDIRECT("CP!"&COLUMN()&":"&COLUMN()))

but this works:

     =SUMPRODUCT(INDIRECT("CP!"&SUM(ROW())&":"&SUM(ROW())),INDIRECT("CP!"&SUM(COLUMN())&":"&SUM(COLUMN())))

Got the answer from http://stackoverflow.com/questions/22746000/using-indirect-function-in-the-sumproduct-formula-for-true-false-cell-refere:

I'm not sure why you need INDIRECT instead of ordinary cell references but the specific problem here is that ROW function returns an "array", even when it returns a single value, e.g. it returns {"x"} rather than just "x" - and in some circumstances Excel can't process that.

Try wrapping the second ROW function in a SUM function - the value doesn't change but it gets rid of the array, i.e.

=SUMPRODUCT((INDIRECT("A2"):INDIRECT("A"&(ROW()-1))=INDIRECT("A"&SUM(ROW())))*1)

This will eliminate #VALUE! eror while leaving the essential structure of your formula unchanged.