Tuesday, December 23, 2014


If you want to rotate an entire table 90 degrees then use PROC TRANSPOSE.  But this PROC defaults to transposing numeric variables only.  So use var _all_ all follows:

data work.grandkids;
input name $ gender $ age mom $ dad $ ;
Kamina  F 8 Cora Peter
Raelani F 6 Cora Peter
Elliott M 3 Cora Peter
Callie  F 1 Emma Nathan

* defaults to numeric variables only;
proc transpose data=work.grandkids out=work.t1;

* try one field such a name but it does not work ;
proc transpose data=work.grandkids out=work.t2;
var name;

* use _all_ to transpose the whole darn thing ;
proc transpose data=work.grandkids out=work.t3;
var _all_;

data work.junk;
input v1 $ v2 $ v3 $ v4 $ v5 $ v6 $ v7 $ v8 $;
i want each word in its own row

* this works ;
proc transpose data=work.junk out=work.t4;
var v1 v2 v3 v4 v5 v6 v7 v8;

* this is easier ;
proc transpose data=work.junk out=work.t5;
var _all_;

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
    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";

And here is the script:

#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:


but this works:


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.


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

Tuesday, September 23, 2014

Java: Including jar files

To compile:

/software/java64/jdk1.7.0_60/bin/javac -cp ~/class -d ~/class -extdirs ~/jars ~/src/MyProgram.java

To execute:

/software/java64/jdk1.7.0_60/bin/java -Djava.ext.dirs=/privdir/xcnb804/jars -cp ~/class MyProgram

Where I got burned was this: while compiling you can use the tilde on the -d option, but while executing you cannot use the tilde on the java.ext.dirs system property.

Monday, September 15, 2014

Java: Get system property from the command line

java -Dmy.prop="my value" MyApp

public class Main {
  public static void main(String[] argv) throws Exception {
    String prop = System.getProperty("my.prop");

Tuesday, September 9, 2014

SAS: proc sql nowarnrecurs

If your PROC SQL overwrites one if its inputs then you will get a warning message.  You can eliminate that message by using the nowarnrecurs option.  For example:

proc sql noprint nowarnrecurs;
create table work.a as
select * from work.a;

Wednesday, August 13, 2014

SAS: Can I use a macro variable within a macro name? (Yes)

I have need to use a macro variable within a macro name. (Sounds crazy, I know, and it borders on writing "write-only code", but it is a legitimate need.) Not knowing if this was possible, I wrote the following program:

* Can I use a macro variable inside a macro name? ;
* This would allow me to iterate through a series of macros. ;

%macro mac1;
%put mac1 was executed;
%mend mac1;

%macro mac2;
%put mac2 was executed;
%mend mac2;

%macro mac3;
%put mac3 was executed;
%mend mac3;

%macro main;

%do i = 1 %to 3;

%mend main;


Output was as I had hoped:

mac1 was executed
mac2 was executed
mac3 was executed

So the answer to my question is Yes.

Tuesday, August 12, 2014

SAS: Number of hours between two DATETIME variables.

I tried several methods to get the number of hours between two DATETIME variables, but they were wrong.  Then I stumbled upon this:  http://support.sas.com/kb/24/585.html

/* Sample 1 - Calculate the difference between two SAS dates           */
/*                                                                     */
/* A SAS date value represents the number of days between January 1,   */
/* 1960 and the specific date.  To produce the difference in days      */
/* between two SAS date values, take the more recent date and subtract */
/* the other date.                                                     */

data dates; 

  /* Date constant syntax -- 'ddmmmyy'd */                             
  put "Difference in " days=;              
/* Sample 2 -- Calculating the difference between two SAS datetimes    */
/*                                                                     */
/* A SAS datetime value represents the number of seconds since         */
/* midnight of January 1, 1960 and the specified datetime.  To         */
/* produce the difference in seconds between two SAS datetime values,  */
/* take the greater of the two datetime values and subtract the other  */
/* datetime.  To convert the difference in seconds to represent the    */
/* difference in hours, divide the difference in seconds by 3600       */
/* (60 seconds x 60 minutes) and use the ROUND function to round the   */
/* value up to the nearest hour.  To convert the difference in seconds */
/* to represent the difference in days, divide the difference in       */
/* seconds by 86400 (60 seconds x 60 minutes x 24 hours) and use the   */
/* ROUND function to round the value up to the nearest day.            */                                                                        
data datetime;  

  /* Datetime constant syntax -- 'ddmmmyy:hh:mm:ss<.s>'dt */                                                        
  put "Difference in " seconds= ;      
  put "Difference in " hours= ;        
  put "Difference in " days= ;         

Tuesday, May 6, 2014

SAS: List of macros in a compiled macro libary

Here's how to see the list of macros in a compiled macro library (where /dev/code/mymaclib contains sasmacr.sas7bcat):

libname maclib "/dev/code/mymaclib";
options mstored sasmstore=maclib;

proc catalog catalog=maclib.sasmacr;

Tuesday, February 25, 2014


Turns out you can put a DROP on a PROC SQL CREATE TABLE.  I'm not sure why I would want to do so vs. just leaving it off the SELECT, but maybe some day.  Anyway, here's how:


data work.grandkids;
input name $ gender $ age;
Kamina F 7
Raelani F 5
Elliott M 2
Callie F 0

proc sql noprint;
create table work.females(drop=gender) as
select name, gender, age
from work.grandkids
where gender = "F";

title "Granddaughters";
proc print data=Work.Females;


SAS: Exception caught in FSCREATE. ERROR: Variable COUNT not found.

This is one of those errors I probably won't see again for a couple of years, and I will have forgotten the fix. Hopefully I will remember this post.

ERROR: Exception caught in FSCREATE.
ERROR: An error occurred in the interval detection procedure. ERROR: Variable COUNT not found.

Cause was an empty input dataset.  We had written a program which cycled through a control table containing segmentation values, and then subset the data by that value, but in this case there was no such value, and FSCREATE was attempting to process an empty dataset.

You might want to look for something like this to see if you had the same problem...

NOTE: There were 0 observations read from the data set 

SAS: Change location of WORK folder

This is something I always forget how to do, and have to relearn it for failure of committing it to a place like this.  Found this in an old email and repeating it here.  (Your path may differ.)

To change the location of the work files, edit E:\SAS\Config\Lev1\SASApp\sasv9_usermods.cfg.  You should see something like this:

/* Setup the default SAS System user work folder */

Note there is no semi-colon on the –WORK line.

Updated 20140226.

Also check C:\Program Files\SASHome\SASFoundation\9.4\nls\en\sasv9.cfg
In this case I tried making a sasv9_usermods.cfg but it appeared to be ignored.
Always a good idea to make a backup file first as I did here:

You can check by running PROC OPTIONS and find "WORK=" in the log.