SAS: Get file creation date

I needed to know when a SAS file had been created, because I did not want a nightly process to replace the file if it had been manually created that same day.  This code will get the creation date of a file (any file, not just a SAS file) and save it as a SAS date in a macro variable.

I got burned with this, not knowing that Windows and Linus would return the date in completely different formats!

     Windows returned:  Wed May 29 22:02:21 2013
     Linus returned:  29May2013:22:02:21

Hence the use of the substr function below.

* What is the creation date of the existing file? ;
* See http://support.sas.com/kb/38/267.html ;
data _null_;
rc = filename("myfile", "full_path_and_filename_here.ext");
put rc=;
fid = fopen("myfile");
infonum = foptnum(fid);
do i = 1 to infonum;
    infoname = foptname(fid, i);
    infoval = finfo(fid, infoname);
    put infoname= infoval=;
    if (infoname = "Last Modified") then do;
        if ("&SYSSCPL" = "Linux") then do;        * Wed May 29 22:02:21 2013 ;
            year = substr(infoval, 21, 4);        * 2013 ;
            month = substr(infoval, 5, 3);        * May ;
            day = substr(infoval, 9, 2);          * 29 ;
            time = substr(infoval, 12, 8);        * 22:02:21 ;
            together = trim(day) || trim(month) || trim(year) || ":" || trim(time);  
            * Now it is 29May2013:22:02:21, same as Windows now ;
            put together=;
            createDateTime = input(together, anydtdtm20.);
            createDate = datepart(createDateTime);
        end;
        else do;
            createDateTime = input(infoval, anydtdtm20.);
            createDate = datepart(createDateTime);
        end;
        put createDate=;
        call symput("CREATE_DATE", trim(left(createDate)));
    end;
end;
close = fclose(fid);
run;

%put *** CREATE_DATE=&CREATE_DATE;

SAS - See if macro variable exists; delete macro variable

This program shows how to see if a macro variable exists, as well as how to delete a macro variable.

%let chk = %symexist(myvar);
%put chk = &chk;        /* output: chk = 0   */
%let myvar = xyz;
%let chk = %symexist(myvar);
%put chk = &chk;        /* output: chk = 1   */
%symdel myvar;          /* delete macro var  */
%let chk = %symexist(myvar);
%put chk = &chk;        /* output: chk = 0   */
%symdel myvar;          /* gives a warning   */
%let chk = %symexist(myvar);
%put chk = &chk;        /* output: chk = 0   */
%symdel myvar /nowarn;  /* gives no warning  */
%let chk = %symexist(myvar);
%put chk = &chk;        /* output: chk = 0   */


Here is the log:

NOTE: Copyright (c) 2002-2010 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.3 (TS1M1)
      Licensed to FIRST ANALYTICS, Site 70073576.
NOTE: This session is executing on the W32_7PRO  platform.



NOTE: Updated analytical products:

SAS/STAT 9.3_M1, SAS/ETS 9.3_M1, SAS/OR 9.3_M1

NOTE: SAS initialization used:
      real time           3.98 seconds
      cpu time            3.05 seconds

1    %let chk = %symexist(myvar);
2    %put chk = &chk;        /* output: chk = 0   */
chk = 0
3    %let myvar = xyz;
4    %let chk = %symexist(myvar);
5    %put chk = &chk;        /* output: chk = 1   */
chk = 1
6    %symdel myvar;          /* delete macro var  */
7    %let chk = %symexist(myvar);
8    %put chk = &chk;        /* output: chk = 0   */
chk = 0
9    %symdel myvar;          /* gives a warning   */
WARNING: Attempt to delete macro variable MYVAR failed. Variable not found.
10   %let chk = %symexist(myvar);
11   %put chk = &chk;        /* output: chk = 0   */
chk = 0
12   %symdel myvar /nowarn;  /* gives no warning  */
13   %let chk = %symexist(myvar);
14   %put chk = &chk;        /* output: chk = 0   */
chk = 0

Here are the SAS doc links:
For symexist:  http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a002635883.htm
For symdel:  http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a001651249.htm

SAS - Temporary variables

Lifted this from http://www.afhood.com/blog/?tag=temporary-variables

This is a great practice that we picked up from the guys over on SAS-L. It is a way to create temporary variables in datasteps that are dropped before being written to the output dataset.

data output_dataset (drop=_:);
set input_dataset;
_temp_var = ;
new_variable = ;
run;

The variables beginning with the underscore ("_") will be dropped before being written to the output dataset. It doesn't matter if there is one temp variable or 1000. As long as they begin with an underscore, they will not make it to the output dataset.  If you use this drop statement without creating any temporary variables you will get a warning.

Just the information I was looking for this morning! Thanks.

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;