Creating a search engine in SAS

Its the age of the search engine! I remember people "yahoo"ing during the late 90's and "Google"ing till the late 2k's and now "Bing"ing.

I just wondered.. Why not SAS? So I started off by doing some reading on the yahoo search engine API's. They have new API released, called as the BOSS. Its documentation is provided here: http://developer.yahoo.com/search/boss/boss_guide/

Next step was to fetch a api key which was generated after i filled out their form. Using this, i could start accessing their BOSS api...

I used the proc http to access the BOSS api using the program below:

filename in "C:\test\curr_in";
filename out "C:\test\curr_out.txt";

data _null_;
title;
if (_N_ eq 1) then do;
 file stdout;
 infile stdin;
 put @1 "Enter the search text:";
 input n $;
 var='appid=xxxxxxx&query='||compress(n)||'&results=1';
 file in;
 put var $;
end;
run;


proc http in=in out=out url="http://search.yahooapis.com/WebSearchService/V1/webSearch" method="post" ct="application/x-www-form-urlencoded";
run;

The above program picks the input from the user, which would be the text that needs to be searched; creates a file curr_in which contains the parameter that needs to be sent out to the BOSS api and posts it to the api using the proc http procedure.

Note that the api key has been typed as xxxx which can be replaced by the api key that you would generate from the developer.yahoo.com site.

Once the program is executed, we can see that the output of the api has been dumped into the curr_out file, which contains the search result in the form of XML. This xml is then parsed using the suitable mechanism to fetch the needed fields and then output it to the stdout. This is accomplished by the below code:

data new;
infile out lrecl=10000 truncover;
input @1 rec $1000.;
if(index(rec,'<Summary>')>0) then do;
 title= substr(rec,index(rec,'<Title>')+7,index(rec,'</Title>')-(index(rec,'<Title>')+7));
 summary=substr(rec,index(rec,'<Summary>')+9,index(rec,'</Summary>')-(index(rec,'<Summary>')+9));
 url = substr(rec,index(rec,'<Url>')+5,index(rec,'</Url>')-(index(rec,'<Url>')+5));
 output;
end;
run;
data _null_;
set new;
file stdout;
put "Title: " title;
put "Summary: " summary;
put "Url: " url;
run;

This produces the output as shown below:

Let me know your feedback/comments!

Making SAS Interactive (Part 2): Using %window and %display

Its the world of GUI. And you are left nowhere if you don't provide the users, the luxury (or rather fulfill the basic needs) of giving a Graphical User Interface. This holds good even for SAS.

SAS came up with the tool called SAS/AF and SAS EG which provides the users with a brilliant GUI and thus making their lives so eeaasssy.. However, not many of us would have bumped into this brilliant macro tool called %WINDOW and %DISPLAY which is present in BASE SAS9 which satisfies the basic needs of having a GUI. Here I take a small dive into the SAS ocean again, exploring the functionalities of these two macros.

%WINDOW: This creates the basic window that needs to be popped up upon execution. We can specify the window attributes here like the color/width/height,etc. You could also specify the position of the text that is to be displayed and the input parameters that are to be read.

%DISPLAY: This actually invokes the window that has been defined in the %window program.

Below is a simple illustration of the %window and %display invocation:

%window test_win color=blue

/*dimension for the window*/
icolumn=15 irow=10
columns=90 rows=45

/*Content of the window*/
#3 @25 "My Window"
attr = rev_video
#5 @10 "Hello Pramod"
attr = underline
;

%display test_win;

The above code displays the output as shown below:

Below, I've demonstrated how we can use this to make SAS interactive. Most of the code is self explanatory, of course with a bit of help from the support.sas.com documentation available at: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000206734.htm

/* Main window */

%window final color=gray

/*dimension for the window*/
icolumn=15 irow=10
columns=90 rows=75

/*Content of the window*/
#1 @25 "&error_msg"
#3 @15 "Hi &sysuserid." attr = rev_video color=blue @60 "Date: &sysdate9." attr = rev_video color=blue
#5 @25 "Welcome to the Reporting World" attr = rev_video
#8 @35 "Report List"
#11 @15 "1. Class listing" @50 "2. Class Report by Gender"
#13 @15 "3. Class Report by Age" @50 "4. Class freq"
#16 @15 "Enter your Choice:" @35 choice attr=underline
;

/* End of Window Final */

%macro execute;
%let choice=;%display final;

%if &choice=1 %then %do;
 proc print data=sashelp.class noobs;
 run;
%end;%else %if &choice=2 %then %do;
 proc report data=sashelp.class nowd;
 columns sex height weight;
 define sex / group;
 define height /analysis;
 define weight / analysis;
 run;
%end;%else %if &choice=3 %then %do;
 proc report data=sashelp.class nowd;
 columns age height weight;
 define age / group;
 define height /analysis;
 define weight / analysis;
 run;
%end;%else %if &choice=4 %then %do;
 proc freq data=sashelp.class;
 tables sex*age /nocum nopercent;
 run;%end;
%mend execute;

%execute
In the above code, I first display a list of things the use might be interested to see in the %window, and then %display this inside the macro execute. I also read his input into the macro variable choice and then based on his selection, I call the required procedure inside the macro execute.

The output of the above code is as shown below:

Upon entering the value 2 and hitting the enter button, we get the output as shown:

You could experiment more on this and let me know your suggestions/thoughts/ideas...

Reading a table from a website into a SAS dataset

Many a times we may want to read a table from the webpages into our datasets. This may be a requirement especially when I would want to analyse the stock market shares and their corresponding trends over the past. This can be done in many ways depending on the web application that is in consideration.

Here I discuss the filename url and the other related methods to access the static webpage which we see in the browser. However, there are many other different methods like FTPing the webpage through some mechanism and then parsing the html/aspx source tags to get the required data, etc.

Now that the Cricket world cup is here, i've decided to use the http://www.espncricinfo.com/ to show how we can read the scorecard into our datasets. I've used a match score card which appears like this in the website:



To begin with, we need to assign a filename to the url where the table resides, by using the filename url syntax:

filename fn url "http://www.espncricinfo.com/icc_cricket_worldcup2011/engine/match/473333.html";

Now that the fileref has been added, we try reading the file into the dataset using the infile/input statements in a datastep:

data _null_;
infile fn lrecl=30000;;
input col1 $10000.;
file "~/test.txt";
put col1 $10000.;
run;

Note: The default value for the lrecl (where we specify the maximum record length in the file), is 256 characters. However, one can specify a value upto 32767. I've specified a length of 30000 just assuming that the html file max length would be 30000.

The above code reads one entire line from the url specified (which essentially contains a html file) into the SAS as a single character variable of length 1000 and writes it into a file named test.txt.

When we open the test.txt to read the contents of the html, we see a lot of html tags which needs to be parsed into a dataset to get the table of our choice.

In the test.txt, we see the following tags appearing in the file as shown below :

<td width="192"><a class="playerName" href="http://www.blogger.com/icc_cricket_worldcup2011/content/player/35263.html" target="" title="view the player profile for Virender Sehwag">V Sehwag </a>&nbsp; </td>

<td class="battingRuns">23</td>

<td class="battingDetails">30</td>

<td class="battingDetails">0</td>

Now, all we need to do is look out for the occurrences of  the text tag: 'class="playerName"' to fetch the player name; 'class="battingRuns">' to fetch the player score; 'class="battingDetails">' to fetch the player matches, and so on and so forth..

This can easily be done by using the following data step code:

data inp;
infile "~/test.txt" lrecl=30000;
input @'class="playerName"' name1 $300. @'class="battingRuns">' runs1 : $20. @'class="battingDetails">' matches1 : $20.;
run;

The above code searches for the occurrences of the text 'class="playerName"' and reads 300 characters following it into the variable name1. Similarly, it also searches for the occurrence of the text 'class="battingRuns"' and 'class="battingDetails"' and reads upto the next 20 characters until it encounters a space (the default delimiter).

Now the first observation of the dataset inp contains the following values:

Name1	Runs1	Matches1
href="http://www.blogger.com/icc_cricket_worldcup2011/content/player/35263.html" target="" title="view the player profile for Virender Sehwag">V Sehwag </a>&nbsp; </td>	23</td>	30</td>

Now, we need to extract the name (V Sehwag) from the name1 variable. This can be done by picking the index of </a> and the string '">' from the value of the variable name1. The function is as follows:

x=index(t,'</a>');
y=index(t,'playerName');
name=substr(t,x+4,y-x+4);

The above set of functions calculate the x and y indices which is the beginning point and the ending point of the string V Sehwag. Then i do a substr of the string knowing the beginning position and the ending position.

To extract the numeric value from the character value, we use the following function:

runs=input(compress(lowcase(runs1),'abcdefghijklmnopqrstuvwxyz<>/'),8.);

Now, coming all the above set of functions, a data step can be built which would give us the final set of data as follows:

data final;
set inp;
x=index(name1,'</a>');
y=index(name1,'playerName');
name=substr(name1,x+4,y-x+4);
runs=input(compress(lowcase(runs1),'abcdefghijklmnopqrstuvwxyz<>/'),8.);
matches=input(compress(lowcase(matches1),'abcdefghijklmnopqrstuvwxyz<>/'),8.);
run;

Thats it for now.. Let me know your experiences or suggestions on doing this in a better way...

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.