Using SAS as my makeshift alarm: Using sound function

I have trouble waking up early everyday. And the days when i forget to set alarm in my mobile, I might wake up two days later!!

I've overcome this problem after i discovered this the sound function in SAS. This function produces a beep sound for the specified frequency and time which are to be supplied as the arguments.

Now, lets see how we make the conventional alarm sound which would ring for 20 times:

data _null_;
do j=1 to 20;
   do i=1 to 4;
      sound(550,500);
   end;
   sleep(1000);
end;
run;

Now, if we schedule this code in a windows scheduler to run everyday at some designated time, SAS would take up the task of waking you up everyday.

I use this sound function more often at the end of my long running programs. It would beep after the completion of the program (yes.. pretty similar to the oven) so that we could do the needful actions.

Just to note that this is possible only with the Windows SAS and the sound function does not work in UNIX/Mainframes.

I would like to end this post by sharing with you, a brilliant application of this sound/sleep function: the composition of the "Ol Mac Donald" Song:

http://www2.sas.com/proceedings/sugi29/048-29.pdf

So.. Let the music begin!!!

Making SAS Interactive (Part 1): Using stdin and stdout

Many a times, we may come across a need for having a dynamic programs. Meaning, we may need the user to key in the input and run the code accordingly, based on his input. This can be achieved in SAS by using the automatic file descriptors: stdin and stdout. This is more widely used in UNIX environment, especially when we batch submit the code in the command line.

In the below code, I illustrate the use of stdin and stdout by implementing a simple calculator, which takes in the numbers and the operators as the arguments and outputs the results.

data test;
if (_N_ eq 1) then do;
 file stdout;
 infile stdin;
 put @1 "Enter the first variable:";
 input X @;
 put @1 "Enter the second variable:";
 input Y @;
 put @1 "Choose the operator: + - * / **:";
 input op $;
end;
retain X Y op;
select (op);
 when ('+') result=X+Y;
 when ('-') result=X-Y;
 when ('*') result=X*Y;
 when ('/') result=X/Y;
 when ('**') result=X**Y;
 otherwise ;
end;
put "The result is:" result;
run;

In the above code, I've redirected the infile and file statements to the stdin and stdout respectively. So the input is always read through the terminal key and the output is always written to the terminal.

When we run the above code in the batch mode, we get the following output:

We can also route the output of a procedure into the terminal using the proc printo as shown below:

proc printto print=stdout;
run;

The below code would output all the details of the student whose name is keyed in the terminal for the sashelp.class dataset:

data name;
title;
if (_N_ eq 1) then do;
 file stdout;
 infile stdin;
 put @1 "Enter the student name:";
 input n $;
end;
retain n;
call symput('name',n);
run;
proc printto print=stdout;
run;
options nodate nonumber;
proc print data=sashelp.class noobs;
where name="&name";
run;

This would give us the below output:

Let me know if you guys have any thoughts or other approaches.

More to come: Making SAS Iinteractive (Part 2): Using window prompts

Send Seasons Greetings - in SAS

On this festive season, you can send cool animated images to your loved ones - a la SAS way!!! Here is how you do it.

• Add a filename email with the required to/cc/bcc id's.
• Include the HTML img tag and give the following source path as shown below.
• You can also hyperlink it to your website if you might want to...

FILENAME mail1 EMAIL
TO=("getpramod.r@tgmail.com" )
From =("getpramod.r@gmail.com")
SUBJECT ="Season's Greetings"
type="text/html"
CT= "text/html" ;


DATA _NULL_;
FILE mail1;
PUT ' Wish You a Merry Chirstmas!!! ';
PUT 'https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCaGnPq1eSjeP0gsO1yQ7PS2xrmQhMkhtPWcJfN5QaF5lHMJA93CEEGuxT-d9yK0UWMb5ndTTrx-W8eTKZX-UWxWmWFtqsTThpmTH1LZZ7LdWwQrfbzGI44clJ701r6BYJqq5kU739GjiM/s320/merry_christmas_animated.gif';
PUT 'And a Happy New Year!!! ';
PUT 'https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbP_vaGfAyMMH-BzhNgPBiRP8mzpYmwltMOzrktekVTA49XDRPjVmwgvQivWXcLK9Xgnn0M-CFCTQ00jX9C2Bi65WVXu2WvC7W0ezqRGx8xZfDOoXAbwib5yAJJV8PXbW_XDWDZq2KGPW2/s320/HappyNewYearAll.gif';
run;

Execute the above code and lo-behold!! You get an animated gif in your mail body as shown below:

Wish You a Merry Chirstmas!!!

And a Happy New Year!!!

Wish you all a Merry Christmas and a Prosperous New year!!!

Resolve vs Symget

Symget function enjoys lot more amount of pulicity and usage as compared to the resolve function, though the later one actually is (at least according to me..) more efficient, powerful and more flexible! (Guess am becoming more sentimental these days..)

Having this in mind, i tried searching about some articles on resovle function in google but in vain. There are lot more number of articles and examples of symget usage and call execute functions as compared to the resolve functions which accomplishes both these functionality.

Resolve function resolves the value of the text expression during the data step execution. It can reslove the value of a macro variable (like symget) and also expand the macro invokation (somewhat similar to call execute, just that it expands the macro and doesn't execute it...)

I've illustrated below, a few simple examples of various uses of Resolve funtion.

Illustration 1: Macro variable resolution in a datastep (similar to symget)

data t;
dt = symget(sysdate9.);
dt1 = resolve('&sysdate9.');
run;

Both the variables in the above datastep returns a character variale of length 200 each having the value of the current date in date9. format.

Illustration 2: Mutiple macro variable resolution in a datastep (extention from symget)

data t;
dt = symget('sysdate9')||' '||symget('systime');
dt1 = resolve('&sysdate9. &systime.');
run;

We can also use resolve function to resolve multiple macro variables (which is unavailable in symget).

Illustration 3: Expansion of a Macro using resolve

%macro min;
select min(age)
from sashelp.class
%mend min;


%macro m(i);
proc sql;
create table tab12 as
select *
from sashelp.class
where age=&i;
quit;
%mend m;

data _null_;
call execute(resolve('%m(%min)'));
run;

In the above example, I use resolve function along with the call execute function which would expand the macro invokation twice (though a single resolve function is being used). Thus, the above data step does a call execute once and resolves the %m which inturn takes the arguement as %min which in turns expands. So the result code would be like this, which is shown in the log:

NOTE: CALL EXECUTE generated line.

1 + proc sql;
1 + create table tab12 as select * from sashelp.class where age=select min(age) from sashelp.class;
1 + quit;

NOTE: Table WORK.TAB12 created, with 2 rows and 5 columns.

Illustration 4: Conditional execution and execution in a data step loop

%macro sql(i,minage,maxage);

%if &i=&minage %then %do;
   Proc Sql Noprint;
   Create table tab as
   select *
   from sashelp.class
   where age = &i
%end;

%else %if &i=&maxage %then %do;
   UNION ALL
   select *
   from sashelp.class
   where age = &i;
   Quit;
%end;


%else %do;
   UNION ALL
   select *
   from sashelp.class
   where age = &i
%end;
%mend sql;

proc sql noprint;
select min(age), max(age) into : minage, : maxage
from sashelp.class;
quit;

data _null_;
do i=11 to 16;
  call execute(resolve('%sql('||i||',&minage,&maxage)'));
end;
run;

In the above example, I'm trying to append multiple datasets which are created out of sashelp.class (for each age values). Here, I'm trying to execute the %sql macro in a datastep loop, and passing both the macro variable and the data step variable as the macro parameters.

This example also shows how to conditionally execute the macro variable based on the parameters passed.

I've pasted below the log message for the above code. Also note that the iteration number is printed at the begining of the every iteration's resolution.

NOTE: CALL EXECUTE generated line.



1 + Proc Sql Noprint;
1 + Create table tab as select * from sashelp.class where age = 11
2 + UNION ALL select * from sashelp.class where age = 12
3 + UNION ALL select * from sashelp.class where age = 13
4 + UNION ALL select * from sashelp.class where age = 14
5 + UNION ALL select * from sashelp.class where age = 15
6 + UNION ALL select * from sashelp.class where age = 16;
6 + Quit;

NOTE: Table WORK.TAB created, with 19 rows and 5 columns.

Booooom!!! proc explode!!!

I came across an interesting procedure today.. Its called as proc explode!

This procedure enables the user to blow up the text in nice formatting and display it on the output screen (I think only in listing).

This reminds me of the 'banner' command in the ol' UNIX boxes.

You can try the explode procedure yourself by copy pasting the below code..


proc explode;
parmcards;
HELLO WORLD
;

And the output is as shown below:



Just a word of caution! Please be mindful about the space before HELLO WORLD in the proc explode step. It throws up an error if we forget that space because SAS expects a numeric or some specific characters in that place (some options for changing the formats of the display).

Also if you are using versions <= SAS 9.1, then you may have to execute this filename statement before the proc explode (Some bug i found!!! )
FILENAME FT15F001 '~/file1.txt';

More information about the proc explode can be got at:

http://www.sfu.ca/sasdoc/sashtml/proc/z0146882.htm

Password encryption

Many a times we come across a situation where we may need to encrypt the password which we use in the SAS programs (Example: Using a password to access a database like db2). This can be acheived by the pwencode procedure. See the example below:

filename fileref "C:\MyFolder\Pwd.txt";
proc pwencode in="My Passwd" out=fileref;
run;

The contents of the Pwd.tx is as follows:
{sas001}TXkgUGFzc3dk

Here the {sas001} denotes the method of encryption. More information on the encryption methods available/used in SAS can be found at:
http://support.sas.com/documentation/cdl/en/secref/62092/HTML/default/viewer.htm#a002595992.htm

Now the encoded password can be used everywhere by reading the contents of the file Pwd.txt.

Error Codes for libname

Whenever we assign a libname (especially a libname to a database), we would want to be sure that the library path should be valid/existing. When it comes to assigning a libname to a database, example oracle, we may also want to confirm if the user-id and the password are valid.

Below is the syntax for doing a error check immediately after assigning a libname and before proceeding to access the lib reference:


libname dbase oracle user="USER_ID" pass="PASSWORD" path='@PATH' schema=MYSCHEMA;

%macro code_area;
filename sendmail email to=("Pramod.R@xyz.com");
%if &syslibrc = 0 %then %do;
/* RUN THE ACTUAL CODE HERE*/
data _null_;
file sendmail subject="Success";
put / "The code ran into completion.";
run;
%end;
%else %do;
/* THROW THE ERROR MAIL */
data _null_;
file sendmail subject="Failed";
put / "The code ran into ran into problems due to Oracle connection problems.";
run;
%end;
%mend code_area;

%code_area;


Similarly we can also do a check for the filename by using the automatic macro variable - %sysfilrc, which returns a 0 value for successful filename statement and a non zero value if the filename statement failed.

Got dating problems? Use ANYDATE...

Many a times we might come across a situation where we would not be able to determine the informat of the date or datetime variable that needs to be read into the SAS dataset. Or me may at times fail to remember the informat name for a particular date informat.

To address these problems, SAS has come up with a common informat for reading the dates for all types of formats - ANYDTDTEw.

See the example below:

Data dates;
Input cdate $22.;
Cards;
16-apr-07
01-02-07
2007-05-06
02-jun-07
13-sep-2007
01JAN2009 14:30:08.5
;
Run;


/* Convert them to required date format using AnydtdteW */

Data Convert;
Set dates;
Date = Input (cdate, ANYDTDTE21.);
Format date date9.;
Run;

The contents of the dataset would be as shown below:

16APR2007
02JAN2007
06MAY2007
02JUN2007
13SEP2007
01JAN2009

However, if we were to have a date being represented as "02/03/04", then it could be quite confusion for us as well as the SAS compiler to know the exact date informat that is to be read in. To overcome this problem, we use the SAS System option - datestyle.

The possible set of values for the datestyle option are: MDY MYD YMD YDM DMY DYM LOCALE.

See the below example to understand the datestyle option clearly:

option datestyle=dmy;

data test;
format dt date9.;
input dt anydtdte10.;
cards;
02/03/04
;
run;

/*output = 02MAR2004 */

option datestyle=ymd;

data test;
format dt date9.;
input dt anydtdte10.;
cards;
02/03/04
;
run;

/* output = 04MAR2002 */

option datestyle=myd;

data test;
format dt date9.;
input dt anydtdte10.;
cards;
02/03/04
;
run;

/* output = 04FEB2003 */

Collateral damage (Control)

Indexed SAS datasets are at times vulnerable to getting damaged. Especially when you update an indexed dataset. To save this damage, SAS has come up with these options which repairs the dataset and gets back the data to its original form.. (trust me.. It once saved my life! Phew!!!!)

Check the below link for the damage control. I tried the option DLDMGACTION=NOINDEX. This was because my data step had completed and i was not able to access the data. There are many other senarios explained which might be handy sometimes...

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#/documentation/cdl/en/lrcon/62955/HTML/default/a001043251.htm