Dark Secrets of %sysfunc

I've been using the %sysfunc for quite sometime now. I must say that its a pretty handy tool.. Especially when you would want to do some heavy wieght lifting in macros. However, one must be mindful of the following... (can I say shortcomings?? Naa.. not quite...)


Quoting fails in %sysfunc:

It is starkly visible when you try using intnx function in a macro. For example, you would want to build a month incrementor in a loop. You would do something like this:

%macro doit;
%let dt='01JAN2011'd;
%do i=1 %to 12;
%put mon=%sysfunc(month(%sysfunc(intnx('month',&dt,&i))));
%end;
%mend doit;

%doit

(Note: We usually do not need a semi-colon (;) after invoking a macro.. But habits die-hard. I'm trying my best to aviod it..)

The above code fails to run as expected. %sysfunc throws up tantrums saying:

WARNING: An argument to the function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is out of range.

This is purely because the %sysfunc does not like the quotes around the month inside the intnx function. Just remove them and SAS becomes your ever-loyal-man Friday!

Some basic funnctions like put and input doesn't work!!

I was taken aback when i discovered this. (Ya.. I know... I know that i din do my documentation reading properly when i was asked to..)

When I submit this program inside a macro,

%put %sysfunc(put('31Jan2011'd,worddate.));

SAS slaps me with this error message:

ERROR: The PUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found.

But this is unexpected... Now.. How on the earth am i supposed to do the type casting which is an integral part of my life (of course apart from my wife!!)

SAS says that you should be using putn, putc, inputn and inputc instead of the regular put/input functions for numeric/charater variables respectively. Wow.. Problem solved!!!The above code can now be written as:

%put %sysfunc(putn('31Jan2011'd,worddate.));

%Sysfunc can be used with a format specifyer (similar to the put function)

This was one of those 'learnings' I had from my SAS Advanced Certification perparation. %sysfunc could be used along with a format speicifer (thus avoiding the necessity to have put function).

The above function can be further reduced as given beow:

%put %sysfunc(today(),worddate.);

So much for now.. Please let me know your thoughts on this or if you have found anything more interesting to do with the %sysfunc

Am back!!

Hey Guys... I know this blog was asleep for a month or so without any activity.. I owe it to my Advanced SAS Certification preparaion...

And I got Certified!!!!

Now that I'm back.. You can expect more posts on the new things that i found out while preparing for the certification!

Cheers!!

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.