SAS: Careful when using functions in PROC SQL with Teradata!

This is a complicated issue to introduce:

• I need to retrieve data from a Teradata table.
• It will take several iterations due to size of the resulting table.
• So I use macro variables to indicate the FIRST_DAY and LAST_DAY to be retrieved.
• These variables are calculated (within a %DO loop) so they resolve to numeric values, not date constants.
• The object of the WHERE clause is a Teradata timestamp (let's call it TS).
• So I need to use the SAS' DATEPART function: WHERE datepart(TS) between &FIRST_DAY and &LAST_DAY
• No error message, but when I ran the query, I got nothing back.
• Turns out, that the FIRST_DAY and LAST_DAY variables must resolve to date constants for this to work.
• So I added the following: %let FIRST_DAY_AS_DATE_CONSTANT = "%sysfunc(putn(&FIRST_DAY, date9.))"d;
• and: %let LAST_DAY_AS_DATE_CONSTANT = "%sysfunc(putn(&LAST_DAY, &date9.))"d;
• And then I used: WHERE datepart(TS) between &FIRST_DAY_AS_DATE_CONSTANT and &LAST_DAY_AS_DATE_CONSTANT
• Success!

This is discussed at http://support.sas.com/kb/42/491.html

This is why you have to pay for experience!

SAS: Running DOS commands or programs from SAS

Running SAS commands or programs from SAS.  Remember: use X command for DOS (builtin) commands, and SYSTASK for DOS programs!

options NOXWAIT;  * Close window when done.  Do not wait for me to type "exit". ;
x "del &NOAA_FOLDER.all.dat";
systask command "start /wait &PATH_TO_7ZIP_EXE x &FOLDER.&YEAR..tar -o&TEMP -aoa" wait;
systask command "start /wait &PATH_TO_7ZIP_EXE x &TEMP\*.gz -o&TEMP -aoa" wait;
x "copy &TEMP\*.op &FOLDER.all.dat /y";  * concatenate into a single file ;
x "del &TEMP\*.* /q";

Java: Get autoincrement value after insert

In Java, how to get the value of an autoincrement column after inserting a row into a table:


        try
        {
            Statement myStmt = (Statement)connection.createStatement();
            myStmt.executeUpdate(sqlStmt, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = myStmt.getGeneratedKeys();
            rs.next();
            answer = rs.getInt(1);
        }   // end try

SAS: Hash table example

A co-worker came up with a cool example of hash table usage in SAS.  Modified slightly and shown here:

data location;
   input Loc $ LocName $ LocState $ LocZip;
   datalines;
AA Ankorage AL 12345
BB Boise    ID 67890
CC Cisco    CA 65432
;
proc print data=location;
   title 'Location';
run;

/*=============== some shift data ===================*/
data shift;
   input EmpID Date yymmdd10. Hours Loc $;
   format Date yymmdd10.;
   datalines;
11 2012-04-03 19 AA
22 2012-04-03 11 BB
33 2012-04-03 12 CC
;
proc print data=shift;
   title 'Shift';
run;

/*=============== some score data ===================*/
data score;
   input EmpID Date yymmdd10. score ;
   format Date yymmdd10.;
   datalines;
11 2012-04-03 900
22 2012-04-03 555
33 2012-04-03 888
44 2013-02-28 999
;
proc print data=score;
   title 'Score';
run;

/*=============== sort the data ===================*/
/*==  no need to sort location since it       =====*/
/*==  is merged using a hash object           =====*/

proc sort data=shift;
   by EmpID Date;
run;
proc sort data=score;
   by EmpID Date;
run;

/*=============== merge it     ===================*/
data merged;
    merge shift score;
    by EmpID Date;

    /* get the location data with a hash table */
    format LocName $20.;
    format LocState $2.;
    if _N_ = 1 then do;
        declare hash h(dataset: "work.location");    /* create the object */
        h.defineKey('Loc');                          /* define the Key  */
        h.defineData('LocName', 'LocState');         /* define the Data */
        h.defineDone();                              /* this loads it   */
        end;
    rc = h.find();
    if (rc > 0) then LocName ='?';

    score    = coalesce(score,0);
    Hours    = coalesce(Hours,0);

proc print data=merged;
   title 'Merged';
run;