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!!!

Accessing Unix server in windows explorer

There has always been a debate between using a GUI-based, windows-freindly FTP client like WinSCP and a conventional telnet client like PuTTY. One party says that its Easy to use and doesn't need any unix knowledge to perform any operations; and the other says that one needs to have such roboust telnet clients to "keep in touch" with the commands/syntax and accept the fact that not everything in the world is "Winowized".

I did a google fight between these two and here is the winner :
http://www.googlefight.com/index.php?lang=en_GB&word1=Winscp&word2=putty

Anyway, before i digress too much, let me show you a intersting way to access your UNIX servers from windows without downloading any FTP client or a telnet client:

• Open the Internet Explorer and type the following in the address bar and hit enter:
ftp://<user-name>:<password>@<server-name>
• Now you would be able to see your unix home directory in your windows explorer if you are using IE 6. If you are using a IE 8, then click on Page and click on Open FTP Site in Windows Explorer. You may be prompted for a user id and password again.
• Now save this link in you favourites and next time you want to login, you could just open a windows explorer and login using your credentials.

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.

Blogger's paradise!

Recently i was invited to join a website named http://www.sas-x.com/ by  Tal Galili

I suggest everyone to subscribe to this feed. It has the aggregation of many big names in SAS blogging like  Chris Hemedinger, Mark Stevens, Jared Prins, Rick Wicklin, and many more!!

Its a privilege that my blog was added to this website...

All the best Tal!

Indexing in SAS.. In a baby's language

A question at the KBC (Indianized version of Who wants to be a Millionire)

If your answer happens to be anything other than C then this might not be a relevant post for you :-)

There have been innumerable number of posts on indexing in SAS. One such brilliant post is found in the below link..

http://blogs.sas.com/sastraining/index.php?/archives/55-To-INDEX-or-not-to-INDEX....html

To me, the working on a simple index is understood this way...

Note: I've considered a B-Tree index. I'm sure SAS uses other better ways to maintain the index table.

Lets consider an example where we have a dataset with 2 variables ID and Age, and having 100 observations with Id values ranging from 1 to 100.

   Now assume that i would want to execute a query as given below:
     
        Proc Sql;
           Select Id, Age
           From Tab
           Where ID = 77;
       Quit;

Now we will understand what happens to the query in two scenarios: without indexing and with indexing

Case 1 - Without indexing:

Nothing unusual happens :-) Query runs to completion! Just that the SAS Complier starts searching the number 77 sequentially from the beginning and would need to make 77 passes to make the right hit!

Case 2 - With Indexing:

If the Table is indexed, (on ID in this example) then the complier first compares if the key variable (77) is less than 50 or greater than 50. Then in compares if the key variable is greater than 75 or less than 75, and so on.. Until a match is found.

 
Thus the number of iterations here is reduced drastically in comparison to the non indexed table.
 
Note: The example considered above has just 100 observations. This was just for the ease of explaination. In reality, indexing for 100 observations would not change the performance much. However, indexing are found to be pretty efficient when the dataset size is huge.

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.

.netrc in UNIX

I came across this interesting feature when i was trying to FTP a file from a FTP server to my Unix box. I used this convetional method to FTP a file earlier:

ftp -n $SERVER
quote user $LOGIN
quote pass $PASSWORD
cd ..
ls BKP files.txt



The problem with this method is that I would need to store my user id and password in the variable LOGIN and PASSWORD and read it everytime. This can be done by saving these variables in another read only file and then reading them everytime we FTP a file. Also we may need to keep updating our passwords as and when we change it.

.netrc file helps us overcome all these shortcomings. All you need to do is create a file named .netrc in your home directory and paste the following text in it:

machine login password


Then give the following permissions: -rw-------

This would help us FTP the files without having us to provide any login credentials anytime.

Script to remove the subversion folder from project directory

Many a times we would want to remove the subversion folders which are present in the project directory for various reasons. (Eg: Copy the structure to another drive, zip it and mail it, etc..). The subversion folders are present in every directory (Eg: .svn, .cvs, etc...).

This script, when executed in windows command mode, removes all the .cvs from the directory structure.

for /d /r . %d in (.cvs) do @if exist "%d" rd /s/q "%d"

Replace a String in Unix

Now that we know how to find a string in a list of files in the home directory, we may also want to replace the string with something else..

Eg: Replacing a password in all the files as and when it is getting changed.

The command to do this is:

find . -type f -name "*.c" -print | xargs perl -i -pe 's/MYOLDPASSWORD/MYNEWPASSWORD/g'

Find a String in UNIX

We've become so much addicted to find/search functionality these days that I tend to look out for Ctrl+F button when I want to find something on a hard bound book as well!!

I had a requirement where I had to search for all the files in my home directory recursively which contained my db2 password because I wanted to change my password in them. I came across this shell command which I found pretty useful:

find . -type f -name "*.*" -exec grep -l "MYDB2PASSWORD" '{}' \;

It just searches through your home directory for a file (type -f) with any name (-name "*.*"). If you are sure of the type of the file you would be interested in then you can also specify it in the -name option (Eg: -name "*.c" for all C programs, etc).

-exec allows us to execute any command in Unix. Here I'm using the Grep command to search for the text MYDB2PASSWORD.

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 */

Adding Abbreviation in SAS

I am a bad typer. And I have a short term memory loss. Remembering the syntax, typing a lot of code (especially the repeating ones)... Na... Not my cup of tea..

SAS Enterprise Guide (4.1 onwards) has come up with a one shot solution for people suffering from my kind of syndrome - Adding Abbreviation.

Here is how you can go about it:

• Open a new code window in SAS EG
• Go to Code -> Add Abbreviation
  
• You get a window where you can add the abbreviation and the text which needs to be substituted for the Abbreviation used. See the example below:

Here, I have a requirement of using my passthrough syntax every now and then and I tend to forget the syntax, password and sometimes even my user name :-)

So.. I use the Abbreaviation 'Pr' and enter the text (rather Copy & Paste) as shown in the above image. Click Ok.

Next time you would want to enter the code, just type Pr and you get a prompt (Yes... à la Visual Studio IDE)

Now, hit the tab or press Enter and you have the code on your Screen!

You can also edit your Abbreviation by going to Code -> Editor Macros -> Macros. Select the required macros (if any) and Click on Edit.

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

Hello World!

Yippie!! I make my entry in Blogspot!!

Watch out for more posts in days to come....

Doing it in Style!!!

Have you ever wondered how to format your output in the style you want? How to get your favorite header colors, your favorite fonts and maybe even a background image(???) to your report you generate in SAS?

Well, it can all be done in SAS Enterprise Guide using your Style manager. Alternatively you can include your favorite stylesheet css file ( you get a lot of them on google.. Or you can create one on your won...) in your SAS EG..

Here is how you can go about it...

Go to Tools -> Style Manager. Here you find a variety of built-in styles on the left pane. If you see the url column in the Style List, you can notice that these are nothing but the pre defined css provided by the SAS Enterprise Guide.



You can choose any of the predefined styles here. If you would want to have a new style of your own.. then you can do the following:

• Click on Add button
• Select - Add new based on existing style option
• Select minimal in the dropdown menu
• Give name to your style
• Click on Ok button. You can see that a new css file has been created your home directory
• Now go to the Edit option
• You can select the Active element in the left bottom corner and chosee the element to which you need to apply the style. Eg: Choose Title and Note Corner and apply the color you want to add, etc.
• You also have the option to add a new element by clicking on Add element. Eg: Click on the Header available in the Preview window, and click on Add Element, give a suitable name and then add your customization.
• You can similarly customize your table borders, add an image, change fonts, add a custom footnote, etc.

Lastly, if you still are not satisfied with any of these features, you can always download a css from various sites and add them into the style manager by choosing the 'Add new external style' option.

So... The ground is open for you guys to play around and experiment.. Its a neat wizard which allows you to customize your report to a fairly good extent..