Wednesday, December 18, 2013

SAS: Conditional %include

I have a SAS program which is generating SAS programs, and I want to conditionally use %include; that is, if the %include file exists, then %include it.  This code demonstrates how.

* SAS conditional %include ;
* See also http://www.sascommunity.org/wiki/Conditionally_Executing_Global_Statements ;

data _null_;
file "c:\temp\a.txt";
put "a = 1;";
run;

data _null_;
file "c:\temp\b.txt";
put "b = 1;";
run;

%let include_file=c:\temp\a.txt;
%let include_file=c:\temp\c.txt;
%let include_file=c:\temp\b.txt;

data Work.test;
%sysfunc(ifc(%sysfunc(fileexist("&include_file")),
    %include "&include_file";, %put File "&include_file" not found.;));
run;

proc print data=Work.test;
run;


Saturday, December 7, 2013

SAS: Looping thru a macro variable

Source code:

%macro doit(FOREACH=);
%local i j ;
%let j = 1 ;
%do %while(%scan(&FOREACH,&j) ne ) ;
   %let i = %scan(&FOREACH,&j) ; 

   %put &FOREACH &j &i;
   %let j = %eval(&j+1) ;
%end ;
%mend doit;

%doit();                 * none ;
%doit(FOREACH=A);        * one only, no parenthesis ;
%doit(FOREACH=(B));      * one only, with parenthesis ;
%doit(FOREACH=(C,D));    * two, comma separated, with parenthesis ;
%doit(FOREACH=(E F G));  * three, space separated, with parenthesis ;



Output from log:


125  %macro doit(FOREACH=);
126  %local i j ;
127  %let j = 1 ;
128  %do %while(%scan(&FOREACH,&j) ne ) ;
129     %let i = %scan(&FOREACH,&j) 

130     %put &FOREACH &j &i;
131     %let j = %eval(&j+1) ;
132  %end ;
133  %mend doit;
134
135  %doit();                 * none ;
136  %doit(FOREACH=A);        * one only, no parenthesis ;
A 1 A
137  %doit(FOREACH=(B));      * one only, with parenthesis ;
(B) 1 B
138  %doit(FOREACH=(C,D));    * two, comma separated, with parenthesis ;
(C,D) 1 C
(C,D) 2 D
139  %doit(FOREACH=(E F G));  * three, space separated, with parenthesis ;
(E F G) 1 E
(E F G) 2 F
(E F G) 3 G



Friday, December 6, 2013

SAS: Floating point hell

I grew up on COBOL and mainframe Assembler.  I loved packed decimal numbers because you always knew what you had.  I HATE floating point numbers.  I first stumbled upon this issue accidentally while learning C.  The same issue exists in Java.  And SAS. 

I don't have time to elaborate right now.  So I just want to capture the problem.  Here is the SAS log.  compare variables (e and f), (c and h), and (b and g).  In particular, note my use of +1.E-10. Warning ... +1.E-11 gives different results.

1    data _null_;
2    a = 400.4444;
3    i = 4;
4    b = int(a * 10 ** i) / (10 ** i);
5    c = (a = b);
6    d = a * 10 ** i;
7    e = int(a * 10 ** i);
8    f = int(a * 10 ** i + 1.E-10);
9    g = int(a * 10 ** i + 1.E-10) / (10 ** i);
10   h = (a = g);
11   put a= i= b= c= d= e= f= g= h=;
12   run;

a=400.4444 i=4 b=400.4443 c=0 d=4004444 e=4004443 f=4004444 g=400.4444 h=1


Tuesday, December 3, 2013

SAS: Temporary arrays

Spent way too much time last night trying to determine why a program was taking too long to run.  The time to run increased exponentially -- rather than linearly -- as more records were processed.  I determined that it was too much reliance on symget and symput.  When I used a temporary array instead, the program ran very quickly.

The following statement will create an array of numeric variables. Key points with temporary arrays:
  • You must specify the size with a numeric constant (or, as I did here, with a macro variable which evaluates to a numeric constant.)
  • Temporary arrays are automatically retained.
  • Temporary arrays are not written to the output data set.

Note how I was able to initialize all entries to zero.

      array used [&_NVARS] _temporary_ (&_NVARS * 0);


Here I check the value of an element in the array:

      if (used[&i] = 0) then do;

Here I set the value of an element in the array:

      used[save_i] = 1;







Friday, November 15, 2013

SAS: Double dash

Use double-dash to indicate a range of variables (in the order they are defined in the program data vector, I assume).

Source code:

    data _null_;
    input a b c $ d e $ f;
    put b--e;
    datalines;
    1 2 w 4 x  6
    2 4 y 8 z 12
    ;
    run;



Log:

    2 w 4 x
    4 y 8 z



Friday, November 1, 2013

SAS: Concatenate strings and numbers using CATX function

No more trim'ing and left'ing!  SAS' CATX function lets you concatenate strings and numbers!

Source code:

    data _null_;
    attrib result length=$20;
    input name $ date_as_number : anydtdte12. ;
    date_as_string = put(date_as_number, mmddyy10.);
    result = catx("*", name, date_as_number);
    put result=;
    result = catx("*", name, date_as_string);
    put result=;
    datalines;
    Cora 19840723
    Hannah 19880626
    Emma 19900411
    ;
    run;


Log:


    1    data _null_;
    2    attrib result length=$20;
    3    input name $ date_as_number : anydtdte12. ;
    4    date_as_string = put(date_as_number, mmddyy10.);
    5    result = catx("*", name, date_as_number);
    6    put result=;
    7    result = catx("*", name, date_as_string);
    8    put result=;
    9    datalines;

    result=Cora*8970
    result=Cora*07/23/1984
    result=Hannah*10404
    result=Hannah*06/26/1988
    result=Emma*11058
    result=Emma*04/11/1990
    NOTE: DATA statement used (Total process time):
          real time           0.09 seconds
          cpu time            0.04 seconds


    13   ;
    14   run;



Friday, July 19, 2013

SAS: Permissive Delete

I recently had a lot of problems with evening processes failing due to lack of disk space:


ERROR: Insufficient space in file PERM.SRA_ANALYTICS_DSS_T.DATA.
ERROR: File PERM.SRA_ANALYTICS_DSS_T.DATA is damaged. I/O processing did not complete.


My project leader pointed out the following:


From the web… SAS does not overwrite datasets at once, but creates a copy and after successful end of a step, the old dataset is deleted and the intermediate file is renamed.

He suggested we delete the file before attempting to write the new one.  So I wrote a macro to conditionally delete the file.  Back in the old days, some of use mainframers called this a "permissive delete", hence the name of the macro.

Here's the macro:

%macro PERMISSIVE_DELETE(SASFILE=);
%if %sysfunc(exist(&SASFILE)) %then %do;
    proc sql noprint;
    drop table &SASFILE;
    quit;
    run;
    %put *** File &SASFILE deleted.;
%end;
%else %do;
    %put *** File &SASFILE already deleted.;
%end;
%mend PERMISSIVE_DELETE;


Here's the test program:

libname Perm "C:\TEMP";
%let TBL_MY_FILE = Perm.Junk;

data &TBL_MY_FILE;
name = "Bill";
age = 56;
run;

proc print data=&TBL_MY_FILE;
run;

* Should delete ;
%PERMISSIVE_DELETE(SASFILE=&TBL_MY_FILE);

* Should be gone already ;
%PERMISSIVE_DELETE(SASFILE=&TBL_MY_FILE);


Here's the significant portion of the log:

24
25   * Should delete ;
26   %PERMISSIVE_DELETE(SASFILE=&TBL_MY_FILE);
NOTE: Table PERM.JUNK has been dropped.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds


*** File Perm.Junk deleted.
27
28   * Should be gone already ;
29   %PERMISSIVE_DELETE(SASFILE=&TBL_MY_FILE);
*** File Perm.Junk already deleted.



Thursday, June 27, 2013

Unix: System date and time to a variable, and using that variable



Caveat: I am a Unix novice.  There may be better ways to do this, but this worked for me.

Show system date and time in the default format:
$ date
Thu Jun 27 21:23:36 CDT 2013

Show system date and time in yymmdd_hhmmss format (two digit year):
$ date "+%y%m%d_%H%M%S"
130627_212355

Show system date and time in yyyymmdd_hhmmss format (four digit year):
$ date "+%Y%m%d_%H%M%S"
20130627_212416

Save the system date and time in yyyymmdd_hhmmss format to a variable, then show it:
$ var="$(date '+%Y%m%d_%H%M%S')"
$ echo datetime is $var
datetime is 20130627_212437

My requirements: Schedule a SAS program (pd.sas) to run at 4am on June 28th.  Send the SAS log to a file whose name includes the system date and time so we can archive the logs.

Step 1: Create a (script) file called go
#!/bin/bash
ts="$(date '+%Y%m%d_%H%M%S')"
echo "sas pd.sas -log pd_$ts.log" | at 0400 Jun 28
Step 2: Execute a (script) file called go
$ . go

Monday, June 24, 2013

Python: Reading a JSON dataset

I just completed an online Python course.  I have been experimenting and there is much about this language to love!

Here is a simple JSON dataset, called min_max_json.txt:

{
"min": 34,
"max": 82
}


Here is the source code, called read_min_max_json.py, which reads the JSON dataset and calculates the range (defined as max - min):

import json
data = json.loads(open('min_max_json.txt').read())
print("This is what was read in:")
print(data)
min = data["min"]
max = data["max"]
range = max - min
print("The range is " + str(range) + ".")


Here is the output:

This is what was read in:
{'min': 34, 'max': 82}
The range is 48.


Note: Using Python 3.3.2 with IDLE.


Thursday, May 30, 2013

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;




Tuesday, April 30, 2013

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



Thursday, April 18, 2013

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.


Thursday, March 28, 2013

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!

Wednesday, March 20, 2013

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



Friday, March 1, 2013

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;




Friday, February 22, 2013

SAS: Floating Point Exception in PROC GLMSELECT

We encountered a strange error in PROC GLMSELECT

      ERROR: Invalid operation.
      ERROR: Termination due to Floating Point Exception.

We asked SAS Support for help.  I sent them the program and a trivial data set with just three records which would cause this exception when processed.

What was confusing is we have run this code many, many times before.

Well, SAS' recommended fix was to add this statement inside PROC GLMSELECT:

      performance buildsscp=full;

And it worked...



Unix: How much memory is on this machine?

To see how much memory is on a machine running Unix, issue the "top" command from the command line (Putty, etc.)  You'll see the answer on the third line of display, for example Mem: 32844876k total.  Type q to quit the top command.

Tuesday, February 19, 2013

SAS: Getting to know the data

Next time I have to familiarize myself with a crap load of client data for a new project, I am going to do something like this...

libname desktop "C:\Users\Owner\Desktop";
proc contents data=desktop._all_ out=Work.Contents noprint;
run;

data Work.Contents
 (keep = Libname Memname Name Type Length Format);
set Work.Contents;
run;


...and export the results to Excel so I can filter easily.



Friday, February 15, 2013

Excellent command line reference: www.ss64.com

Excellent command line reference (I used it for some batch file stuff but there is so much more available.)

http://ss64.com/



Tuesday, February 12, 2013

SAS: Suppress PROC output

I need to run a SAS PROC repeatedly.  I will append the results to a file.  So I want to suppress the PROC's normal printed output:

    * suppress lengthy proc outputs;
    filename junk dummy;
    proc printto print=junk;
    run;

    * do your thing here ;


    * restart print;
    proc printto;
    run;




Thursday, February 7, 2013

SAS: Reading the directory

I have to import a file which will be created everyday.  IT gets moved to the server automatically by another process.  It has date and time in the filename.  I won't know the time.  How can I import this?  Well, I need to determine the full filename, and to do so I will need to query the directory.

Here's a link to the article which I used:  http://support.sas.com/kb/41/880.html

Here's a subset of my code (which must run on Windows and Linux):

* Get todays date as a macro variable in MMDDYYYY format;

data _null_;
today = input("&SYSDATE9", anydtdte10.);
mmddyyyy = put(today, mmddyyn8.);
call symput("MMDDYYYY", mmddyyyy);
run;

%put &MMDDYYYY;

%macro op;
%if (&SYSSCP = WIN) %then %do;
    filename DIRLIST pipe "dir &SOURCE_LIB.&STARTS_WITH.&MMDDYYYY.*.csv " ;
%end;
%else %do;
    filename DIRLIST pipe "ls &SOURCE_LIB.&STARTS_WITH.&MMDDYYYY.*.csv" ;
%end;
%mend;

%op;

* I do not know full filename.  Has mmddyyyy followed by a six digit. ;
* number, followed by .csv.  I need to isolate that six digit number. ;
* See http://support.sas.com/kb/41/880.html ;
data dirlist ;                                              
infile dirlist lrecl=200 truncover;                         
input line $200.; 
where_is_dot_csv = index(line, ".csv");
if (where_is_dot_csv = 0) then delete;
where_is_date = index(line, "&MMDDYYYY");
number = substr(line, (where_is_date + 8), where_is_dot_csv - (where_is_date + 8));
put number=;
call symput("NUMBER", trim(left(number)));
run;

%let FULLNAME = &SOURCE_LIB.&STARTS_WITH.&MMDDYYYY.&NUMBER..csv;

proc import datafile="&FULLNAME"
    out=Work.Imported
    dbms=csv
    replace;
    getnames=yes
    ;
run;



Friday, February 1, 2013

SAS: Process six months of data at a time

Due to space constraints, I need to process six months of data at a time.  Here's how I will do it:

* How I will process six months of data at a time ;

* These will come from global constants file ;
%let GLBL_EARLIEST_DATE_TO_IMPORT = '01Jan2011'd;
%let GLBL_LATEST_DATE_TO_IMPORT = '01Jan2014'd;

* Show what we got ;
%put GLBL_EARLIEST_DATE_TO_IMPORT = %sysfunc(putn(&GLBL_EARLIEST_DATE_TO_IMPORT, yymmddn8.));
%put GLBL_LATEST_DATE_TO_IMPORT = %sysfunc(putn(&GLBL_LATEST_DATE_TO_IMPORT, yymmddn8.));

* Here is the start of the macro definition ;

%macro doit;

* Starting date is determined by global macro constant ;
%let FIRST_DAY = %sysevalf(&GLBL_EARLIEST_DATE_TO_IMPORT);
%put FIRST_DAY = %sysfunc(putn(&FIRST_DAY, yymmddn8.));

* Will count number of iterations. Count will be used in filename of temp output ;
%let ITERATIONS = 0;

%do %while (&FIRST_DAY <= %sysevalf(&GLBL_LATEST_DATE_TO_IMPORT));

%let ITERATIONS = %sysevalf(&ITERATIONS + 1);

* Last day for this iteration is the last day of the month five months from now ;
%let LAST_DAY = %sysfunc(intnx(month, &FIRST_DAY, 5, e));

* Do not go past the indicated last date to import ;
%if (&LAST_DAY > %sysevalf(&GLBL_LATEST_DATE_TO_IMPORT)) %then %do;
    %let LAST_DAY = %sysevalf(&GLBL_LATEST_DATE_TO_IMPORT);
%end;

* Show values this iteration ;

%put ITERATIONS = &ITERATIONS  FIRST_DAY = %sysfunc(putn(&FIRST_DAY, yymmddn8.))
     LAST_DAY = %sysfunc(putn(&LAST_DAY, yymmddn8.)) ;

* This is where the real work will happen ;
* For test purposes, create a simple dataset with the iteration number ;
data Work.table&ITERATIONS;
a = &ITERATIONS;
run;

* For the next iteration, the first day is the first day of the month six months from now ;
%let FIRST_DAY = %sysfunc(intnx(month, &FIRST_DAY, 6, b));

%end; * do while ;

* Checking ... ;
%put Did loop &ITERATIONS times.;

* Concatenate the work files which were produced ;
data work.concat;
set
%do i = 1 %to &ITERATIONS;
  work.table&i
%end;
; * end the set statement;
run;

* Show concatenated data set ;
proc print data=Work.concat noobs;
run;

%mend doit;

* Ok, go... ;
%doit;

Wednesday, January 23, 2013

SAS: Merge has one END only!

SAS' Merge has one END only.  The indicated variable is set to 1 when both files are at end of file...

    data Work.Updated (keep = Store Date Cost) ;
    merge Work.Master (rename = (Cost = m_Cost) in=in_master)
        Work.Trans (rename = (Cost = t_Cost) in=in_trans )
        end=eof_both ;
    by Store Date;

...and later...

    if (eof_both) then do;
        * do eof stuff ;
    end;  
    run;


SAS: Setting a macro variable to value of SYSCC, which is itself a macro variable.

Here's how to set a SAS macro variable to the value of SYSCC, which is itself a macro variable:

%if (&SYSCC ne 0) %then %do;
   %let ERR_LVL = %sysevalf(&SYSCC);
%end;

Tuesday, January 22, 2013

SAS: Left-align a macro value

I write various data to a process log as name-value pairs.  Sometimes the "values" are character and sometimes they are numeric, so they are defined as character in the database.  I want the "values" to be left-aligned regardless of type.  The name-value pairs are appended to the log via a macro which uses an INSERT statement within PROC SQL.  For some unknown reason, I was unable to use trim(left(x)) in the INSERT statement.  But I stumbled upon a format option which allows left / right / center justification:

%append_to_process_log("#output", put(&OUT, 10. -l));


SAS: Redirecting the WORK files

Here's how to send SAS WORK files to a different drive / folder.

Edit the file (your drive may differ...)
E:\SAS\Config\Lev1\SASApp\sasv9_usermods.cfg

Add these two lines (the first of the two may already be there; if so then the line after as shown here:0
/* Setup the default SAS System user work folder */
-WORK "J:\SASWORK"

Note there is no semi-colon on the -WORK line, which is unlike most SAS stuff.



Monday, January 21, 2013

SAS: log1px funtion

Here's some SAS information I found curious.  I am currently taking a time series course, and in the first homework assignment we are asked to calculate the log returns as lnreturn = log(ret + 1).  This must be a common operation, because SAS has a builtin function for that purpose.  The function is log1px and is used as follows:  lnreturn = log1px(ret);

Big deal, right?  I mean it's not as if the other way (log(ret + 1)) is hard to code.  Yes, but according to the documentation (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003121132.htm), "when x is close to 0, LOG1PX(x) can be more accurate than LOG(1+x)."

(I assume the 1PX stands for "1 plus x".)


Tuesday, January 15, 2013

Ext.js - Load data synchronously

Ext.js loading process is asynchronous: it will not wait for the data to be loaded.  Rather, it will continue processing immediately.  So you cannot expect the data you load to be immediately available.  The following will make the load synchronous.

Before

    launch: function()
    {
        // other stuff too...

        loadComboBoxStores();
  
        // other stuff too...
    }

After

    launch: function() 
    {
        // other stuff too...

        Ext.onReady(function()
        {
            loadComboBoxStores();
        }); 

        // other stuff too...
    }

(I post this reluctantly as I have limited understanding of this.  But my solution appears to work.)

post script:  That didn't work.  But this did...

According to the Ext.js 4 documentation, "Ajax server requests are asynchronous and this call (to get data from server) will return before the response (data) has been received.  Process any returned data in a callback function."

So use of callback is the key.  Callback is an optional parameter of the store.load().

Here is my first (and apparently successful) attempt at a callback function.  Well, actually two callback functions.  First, it dynamically loads a list of stores of combo boxes.  Then it dynamically loads the contents of each of those stores of combo boxes.

There may be better ways of doing this, but it does work...

// Build stores for combox boxes

function loadComboBoxStores()
{
    // Ajax server requests are asynchronous, meaning the call to Ajax will return immediately,
    // before the response from the server has been received.  Returned data should be processed
    // in a callback function.  (There are all sorts of references to this issue on the web.)
   
    // create Model which can be used for all combo boxes
    Ext.define('MyComboBoxModel', {
        extend: 'Ext.data.Model',
        fields: [
            {name: 'value'  , type: 'string'},
            {name: 'display', type: 'string'}
        ]
    });

    var storeOfComboBoxStoreNames = Ext.getStore('ComboBoxStoreNames');
   
    storeOfComboBoxStoreNames.load({
        callback: function()
        {
            for (var i = 0; i < storeOfComboBoxStoreNames.getCount(); i++)
            {
                var row = storeOfComboBoxStoreNames.getAt(i);
                // console.log("store: " + row.data.store);
                Ext.create('Ext.data.Store',
                {
                    storeId: row.data.store,
                    model  : 'MyComboBoxModel'
                } );
            }
           
            var storeOfComboBoxes = Ext.getStore('ComboBoxes');

            storeOfComboBoxes.load({
                callback: function()
                {
                    for (var i = 0; i < storeOfComboBoxes.getCount(); i++)
                    {
                        var row = storeOfComboBoxes.getAt(i);
                        var store = Ext.getStore(row.data.store);
                        var model = store.model;
                        var record = new model( { value: row.data.value, display: row.data.display } );
                        store.add(record);
                    }
                }  // end inner callback
            });    // end inner load
        }  // end outer callback
    });  // end outer load
}







Saturday, January 12, 2013

SAS: Reading and Writing XML

SAS' xmlv2 libname engine makes it easy to read and write XML files!

Here's my source code:


    * Create test SAS dataset ;
    data work.kids;
    input name $ age gender $;
    datalines;
    Kamina 6 F
    Raelani 4 F
    Elliott 1 M
    ;
    run;

    libname myxml xmlv2 "C:\temp\kids.xml";

    * Write SAS data to XML ;
    data myxml.kids;
    set work.kids;
    run;

    * Read XML data into SAS;
    data work.getback;
    set myxml.kids;
    run;

    * Show what you got;
    proc print data=work.getback;
    run;


Here's the output from PROC PRINT:

Obs name age gender
1 Kamina 6 F
2 Raelani 4 F
3 Elliott 1 M


Here's the XML that was written, and then read back in:

    <?xml version="1.0" encoding="windows-1252" ?>
    <TABLE>
       <KIDS>
          <name>Kamina</name>
          <age>6</age>
          <gender>F</gender>
       </KIDS>
       <KIDS>
          <name>Raelani</name>
          <age>4</age>
          <gender>F</gender>
       </KIDS>
       <KIDS>
          <name>Elliott</name>
          <age>1</age>
          <gender>M</gender>
       </KIDS>
    </TABLE>




SAS: Unwanted blank in PUT output

Check this out!  When using the PUT statement, if a variable is followed by a literal, you always get a blank between the two, but not so when a literal is followed by a variable. In the former case, if you don't want that blank there, you must use the column modifier +(-1) to "back up" one space.  Hardly what I would call intuitive!

Here's some source code:

    data _null_;
    a = 123;
    b = -123;
    put "[" a "]";
    put "[" b "]";
    put "[" a +(-1) "]";
    put "[" b +(-1) "]";
    run;


And here's the output:

    [123 ]
    [-123 ]
    [123]
    [-123]




Friday, January 11, 2013

SAS: PROC CONTENTS list variables in order created

I have often been annoyed that SAS' PROC CONTENTS lists variables in alphabetical order rather than the order in which they appear in the dataset.  Turns out there is an option, varnum, which causes the variables to be listed in my preferred order.  Here's some sample code:

    * Demo varnum option on PROC CONTENTS ;
    data work.kids;
    input name $ age gender $;
    datalines;
    Kamina 6 F
    Raelani 4 F
    Elliott 1 M
    ;
    run;

    proc contents data=work.kids;
    run;

    proc contents data=work.kids varnum;
    run;


And some (truncated) output...

    Alphabetic List of Variables and Attributes

           #    Variable    Type    Len

           2    age         Num       8
           3    gender      Char      8
           1    name        Char      8



            Variables in Creation Order

           #    Variable    Type    Len

           1    name        Char      8
           2    age         Num       8
           3    gender      Char      8



Thursday, January 10, 2013

HTML: Width of a paragraph

Here's how to set the width of a paragraph in HTML:  <p style="width:80%;">



Wednesday, January 9, 2013

SAS: Get environment variables using %sysget

%let UserID=%sysget(USERNAME);
%put User is &UserID;

Environment variable (USERNAME here) is case sensitive.

Annoying SAS "Unsupported Device" message in Enterprise Guide

I hate it when SAS Enterprise Guide shows a warning, and the only message is this:

WARNING: Unsupported device 'ACTIVEX' for PDF destination. Using device 'ACTXIMG'.

This can be eliminated by putting this option at the top of the program:

options dev=actximg;  * Prevents the annoying "Unsupported device" message in EG ;


Monday, January 7, 2013

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

My web app was failing with this exception:  com.mysql.jdbc.exceptions.jdbc4.CommunicationsException.  Turns out it was using a database connection pool (see article at http://www.developer.com/java/data/article.php/3847901/Implement-Java-Connection-Pooling-with-JDBC.htm) and the connections were timing out.  Default timeout on MySQL is eight hours.  I fixed this by using the isValid method of the Connection class (code added to getConnectionFromPool method):

try
{
   if (connection == null || !connection.isValid(5))
   {
      connection = createNewConnectionForPool();
   }

}
catch (SQLException sqle)
{
   connection = createNewConnectionForPool();

}

return connection;

Creating a background process in DOS

Creating a background process in Unix is trivial: put a trailing ampersand on the command.  But what about DOS (Windows)? Use the START command as explained in this article:  http://forums.whirlpool.net.au/archive/391278

C:\>start notepad.exe
will start Notepad, and DOS prompt is available for next command

C:\>start /wait notepad.exe 
will start Notepad, and DOS prompt is not available until Notepad is closed.

Wednesday, January 2, 2013

SAS: Continue processing despite errors

I have a SAS macro, used at the end of many programs, to update a submissions file indicating when a program has completed processing and the error code.  But this macro wouldn't work if the program failed.  And yet the log made it look like it was doing something there.  I was unaware of the NOSYNTAXCHECK option. I think the name is misleading.  What it really means is "don't just syntax check".

Here is a link to the SAS documentation:  http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#base-sysop-syntaxcheck.htm

"If a syntax or semantic error occurs in a DATA step after the SYNTAXCHECK option is set, then SAS enters syntax check mode, which remains in effect from the point where SAS encountered the error to the end of the code that was submitted. After SAS enters syntax mode, all subsequent DATA step statements and PROC step statements are validated. While in syntax check mode, only limited processing is performed."

So it was doing some "limited processing", just as the log seemed to indicate (such as resolving macro variables).

Another misleading entry on the log was that DATA steps were executed, but no output written.  I guess they were just syntax checked.

So I modified my macro as follows:

%macro updsub;
options nosyntaxcheck;  * processing regardless of previous errors ;
/* my macro code here */
options syntaxcheck;   * put it back as it (probably) was ;
%mend updsub;

Problem solved!