Filtering Dataset on Html Page

[Yennie]

I am displaying my whole datatset in a html page something as below

ODS HTML Path = "K:/Temp/" File ="Test.html";
Proc print data = new_vehicle;
run;

Proc print data = vehicle;
run;
ODS HTML close;

This is a rough guide of dataset "Vehicle" looks like and sometimes it can go up to more than 1000 obs.

ID Year Make Family Variant Series
KHZ10D 2010 FORD FALCON XR6 FG UPGRADE
KI010D 2010 BMW 320I XR6T FG

As for dataset "New_Vehicle" Everything is exactly the same, except that there will be three extra columns that say "Points", New_Price" and a submit button. Points and New_Price are text fields that requires user to type in a value and the submit button will send the values that had been typed in by users to another mainframe program.

at this stage, I am taking this step by step. questions are:

1. How do I create a filter for each of the titles in "Vehicle" dataset so that to allow the users to search for the vehicles display easily. something similar to the "filter" function in microsoft excel.

2. Is it possible to create the text fields and submit on the html page?

3. How do I get this submit button to send the results to mainframe ?

hope someone can give me some guidance on this one. once again.. I know this is probably sounding really ambitious but i am putting my questions out there and hoping someone has a better thought on this one than me!

thanks heaps!
yennie Message was edited by: Yennie

[sbb]

Your questions regarding a "SUBMIT button" suggest you wish to have some type of generated HTML page form to display, then take a end-user reply (entered in a box after viewing an on-screen report maybe?) and execute another SAS program to generate some type of output? The path/flow is somewhat unclear, at least for me.

Regarding question #3 and "..send the results to mainframe.", what do you expect to happen on the mainframe, specifically? Also, please help by explaining your SAS environment - both on your local computer system and also what SAS components are installed on the mainframe?

Possibly you might consider the SAS Stored Process?

http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/index.html


Scott Barry
SBBWorks, Inc. Message was edited by: sbb

[Cynthia_sas]

Hi:
First HTML is NOT Excel...when you are rendering HTML, you most generally use a browser. An HTML table is "static" -- it is content "marked up" with HTML presentation tags. The content is decided upon, usually, before the HTML page is displayed. The content could be the full text of Moby Dick or Finnegan's Wake (novels) or the content could be bowling scores or the content could be data in a table form. At any rate, once the content is displayed in a browser window, it is generally NOT changeable with "vanilla" HTML.

So your question about filtering what is in an HTML table means that you will either move more into the world of HTML and scripting (Dynamic HTML, PHP, JavaScript, ActiveX, ActiveServer pages, Java Server pages, Cold Fusion, etc) or you will move into the world of advanced SAS products and client/server technology such as SAS/IntrNet and/or SAS Stored Processes using the SAS Addin for Microsoft Office, the SAS Information Delivery Portal, or other part of the SAS Enterprise Intelligence Platform (also known as the BI platform).

Specifically, you asked:

1. How do I create a filter for each of the titles in "Vehicle" dataset so that to allow the users to search for the vehicles display easily. something similar to the "filter" function in microsoft excel.
HTML is not Excel. Generally, the way that you "prompt" the user for information -prior- to the display of an HTML page is with an HTML form. You show the user choices, they hit a SUBMIT button and the HTML form <ACTION> tag tells the browser what to do with the fields that were selected when the SUBMIT button is engaged. If you want more snap, crackle and pop (interactivity) in your HTML pages (without using SAS), then you look to enhance the HTML with other technologies, such as JavaScript or PHP technology.

If you want to create more dynamic HTML with SAS, then you are looking at more advanced client/server types of solutions. If you had the SAS/IntrNet product, then you would use htmSQL to build a dynamic HTML form that showed a list of vehicles and you would allow the users to choose criteria on that first page, the form <ACTION> would then be a call to the SAS Application "broker" with a request for a specific program (a SAS Application Dispatcher program) to be run, using the criteria selected by the users. If, on the other hand, you were using the SAS Enterprise Intelligence Platform, you would use a stored process (SP), as Scott suggested. The prompting framework used with the SP would allow you to prompt the users for their criteria and then when they hit the SUBMIT or RUN button, their criteria would be used in the execution of the SP and the results would come back in whatever form was appropriate (HTML for the Information Delivery Portal, word-processing form for Microsoft Word, HTML for a browser or for Enterprise Guide), Stored Process Web Application, etc). Without knowing what products you have (Base SAS, SAS/IntrNet, SAS Enterprise Intelligence Platform), it is very hard to get into more details on "filtering". For an example of a SAS/IntrNet program, see this site:
http://support.sas.com/documentation/cdl/en/dispatch/59547/HTML/default/sesssamp.htm

To learn more about writing and executing Stored Processes, see this site:
http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/a003152553.htm
http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/datapass.htm


2. Is it possible to create the text fields and submit on the html page?
Yes, the method will depend on the technology you have available.

3. How do I get this submit button to send the results to mainframe ?
If you were using SAS/IntrNet, you would send SAS to a server (could be on any platform, not just the mainframe) via URL, as shown below either in a form <ACTION> tag or in an <A> tag:
[pre]
href="http://your_server_name/cgi_dir/broker.exe?_service=appserv&_program=libname.pgmname.sas"[/pre]

If you were using the Stored Process Web Application, again the submission would be via URL, as shown below:
[pre]
<a target="new" href="http://your_server_name:9090/SASStoredProcess/do?_program=/SP_repos/SP_folder/pgm_name">Click here to run program</a>
[/pre]

If you were using the client applications within the SAS Enterprise Intelligence Platform, then the communication between the client application and SAS on a server is handled by each client application and you do not explicitly have to code the execution information -- that is handled by the configuration of the Platform.

The method you use will really depend on the technology and products you have available. If you Google using the search terms:
SAS Web Applications dynamic

you will find user group papers that describe web applications such as you describe.

cynthia

[Yennie]

Hi Cynthia,

Thank you so much for your reply and it was absolutely helpful to me as always.

Initially, I knew what I wanted to achieve was more than complicated due to my limited SAS + HTML skills.

I had a chat to my boss yesterday, sat down and try to simplify our process. Our main goal is to use a little programs involved as possible. Due to limited resources available in our company, SAS/IntrNet and SAS Enterprise Intelligence Platform are definitely out of the questions. And we are sticking to SAS Dev 9.1, Excel and Mainframe at this stage.

What I am trying to find out is that, at this stage, I am going to output my datasets into Excel (This is definitely a simple process to do so, no qualms about this). Then someone in my team will manually fill in the points and new price of these vehicles on the spreadsheet.

Now here come the hard bit, my question is that... Once these points and prices been filled up, is there a way to use SAS to copy the details of all these vehicles from the spreadsheet and drop the whole batch into Mainframe? This batch process will happen every fortnight and what we would even like to achieve in the future is that structuring this SAS code in a way that it is able to detect the existence of this Excel spreadsheet in a folder every night and drop it into Mainframe.

If so, I really appreciate that there are articles out there that can help me out in making this happen.

Cynthia, I am not sure if this can be done but all in all your help been greatly appreciated, you have gone far and beyond! This forum can't be any better without you!

Cheers,
Yennie

[sbb]

It will be a key point to clarify what is meant by "..drop it into Mainframe."?

Most definitely, SAS functions can be used to detect existence of a file or directory on a remote (or local) system (the FILEEXIST function and others), and continue processing, if found.

Also, FTP can be used within a SAS session/job to retrieve a file on another system, and then do something -- like process the file and generate a report or extract information from the retrieved file and perform some type of data-manipulation processing.

The points suggested are definitely attainable with the SAS system.

Scott Barry
SBBWorks, Inc.

[Cynthia_sas]

Hi:
I agree with Scott -- knowing what is meant by "drop it into the Mainframe" is critical here.

If, for example, you have SAS on a local machine and you have SAS on the Mainframe and you have SAS/Connect, then you could read the Excel file, with the new columns on your local machine and then create a SAS dataset on the Mainframe which contained the new information. (This is similar, but not entirely similar to what would happen if you had EG on a local machine and were communicating with SAS on the Mainframe.)

If you only have SAS on a local machine, then you have different options. This is where the "drop it into the Mainframe" becomes critical. Mainframe files are not like PC files. They can live in proprietary database formats, like Oracle or DB2 or Adabas. A Mainframe file can be sequential -- it can be read with any program on the mainframe or a Mainframe file can be a a file that lives in a Partitioned Data Set; or a Mainframe file could be an older file type like VSAM or ISAM or BSAM format. PC files are in ASCII form in internal storage; Mainframe files are in EBCDIC form in internal storage. Last, but certainly not least, Excel does not live on the Mainframe. So what type of file would you create that would be "dropped" into the Mainframe??? A SAS dataset? A flat file to be read with some other type of program? A database file? What database?

What you want to do is possible. SAS on a local machine could create a sequential file that could be FTP'd to a Mainframe, and usually the FTP process will automatically do the translation from ASCII to EBCDIC. However, once on the Mainframe, what happens to the file? What program will read the file? Where does the file go? Where is the file stored? Generally speaking, SAS would not be able to create a file for a proprietary file format (like Oracle, DB2, VSAM, etc) without having SAS on the Mainframe -and- the appropriate SAS/ACCESS product. Just as you need SAS/ACCESS for PC Files to read Excel files on the Windows platform, you would need SAS/ACCESS for the appropriate file type or DBMS in order to WRITE files in a proprietary format.

Conceptually, what you want to do is possible. Your best resource, at this point, would be to contact Tech Support with this question and they could look at the current suite of SAS products that you have on various Platforms and they might be able to narrow down your options for making this concept a reality. For example, if you do have SAS and SAS/Connect on your Mainframe, then they can point you to examples of programs that use SAS/Connect. If you do not have SAS on the Mainframe, then they can point you to examples of programs that create "flat files" suitable for FTP'ing.

cynthia