SAS, ASP.NET AJAX, Web Services

In preparation for SAS Global Forum 2007, I have posted 2 videos showing 1) How to set up a web service to read a SAS dataset, 2) How to consume that service in an ASP.NET AJAX page. Simple demos but they should help get people started:

There are several videos demonstrating the concepts involved. They can be found here:

http://utilities.savian.net

Look under the video tab.

Code generation, macros, et al

As a SAS programmer evolves, they start to think:

"Hey, I can create SAS code using data step or macros!"

So they start to go down the happy path of code generation:

data _null_;
file "myplace";
put "data test ;
/ " set a; "
/ " x = wereHavingFunFunc(y)" ;
... etc.
run;

...and pretty soon they have elaborate SAS code in macros, etc. that does nothing but generate code. If you don't use my utility SasEncase to help you do this, you are doing a LOT of extra work but that is another story.

Well, I think the next level of thought (at least for me) was:

"Hey! Why can't I just use ANY programming language to generate SAS code"

Now, think about that for a sec. ANY programming language can be used to generate SAS code.

The reason why SAS is an entry into this area is because that is what all of us know and love. But, don't confine yourself to just using SAS for code generation. Instead, pick other languages as the need arises.

public void CreateSomeSasCode()
{
StreamWriter sw = new StreamWriter(@"c:\temp\mySasCode.sas") ;
sw.WriteLine("data test;") ;
....
}

Now, that's better, a little C# to play with.

Ok, so you would have to wrap a lot of code.

I think I have evolved. Now I actually write the SAS code with macro parms and store them on a server. Then you can just call the stored process and pass the parameters using web services:

DataSet ds = SasServer.Services.ExecuteStoredProcedure("mySasCode.sas", @"%let outdata = 'c:\temp'");

Now, I think we have nirvana: SAS doing what it does best being driven by a modern OOP environment.

Dropping flyballs in left field,
Alan

Inserting records into SQL Server

99+% of the time, I read records from SQL Server into SAS. I typically use C# and do it direct in code. However, I recently needed to write records into SQL Server from the SAS side.

Attempt #1 was to use PROC APPEND. This failed with the following:

"ERROR: During insert: Data was not set for one or more columns."

This was failing on the identity column.

Attempt #2 was to try a SQL Server insert:

proc sql ;
insert into SqlSrvr.Test
select * from newdata
;
quit;

ERROR: Attempt to insert fewer columns than specified after the INSERT table name.
ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.
ERROR: Value 2 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.
ERROR: Value 17 on the SELECT clause does not match the data type of the corresponding column
listed after the INSERT table name.

Hmmmm, could it be a problem wit hthe identity column and me using the new XML filed type?

A little bit of sleep and attempt #3 worked:

libname SQLSrvr oledb provider=sqloledb init_string='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyCustomer;Data Source=SERVER01' schema=dbo ;

data NewData;
attrib platform length=$200
periodicity length=$200
level0-level10 length=$200
image length=$1024
data length=$1024
help length=$1024
;
DateTime = DateTime() ;
Platform = "MVS" ;
Periodicity = "Daily" ;
Level0 = "CPU Utilization" ;
Image = "c:\temp\myimage.jpg" ;
Data = "" ;
Help = "c:\temp\myhelp.doc" ;
run;

proc sql ;
insert into SqlSrvr.Test
select * from newdata
;
quit;

proc sql ;
insert into SqlSrvr.Test (datetime,platform, periodicity, level0, image, data, help)
select datetime, platform, periodicity, level0, image, data, help from newdata
;
quit;

SAS and AJAX

SAS has no built in support for AJAX at this time. However, you can hack up some of it by using enabling technologies such as ASP.NET or just code it yourself in JavaScript.

However, I have coded AJAX bits in JavaScript using SAS/IntrNet and found the experience less than desirable. A better way to make this happen, IMO, is to use the new Atlas framework from Microsoft:

http://atlas.asp.net/Default.aspx?tabid=47

It's quick and easy and makes coding AJAX much easier. JavaScript is god-awful due to lack of true debugging support but it works. Make it easier though by focusing on doing the AJAX piece in Atlas and let them handle the JavaScript bits. My $0.01.

Alan

Weird VS2005 Error

I'm posting this in case others hit the same issue.

When trying to doa ClickOnce deployment, we hit the following error:

"Cannot publish because a project failed to build."
"SignTool reported an error. "The parameter is incorrect."

We switched from VB to C# and it worked fine. I'll leave this blog posting out on the net so it can help someone else out doing ClickOnce deployments.

EG Tasks not displaying

Installed EG4.1 and no task were displayed. Here's how I fixed it (based upon an old 2.0 TS post):

Go to:

Tools > SAS Enterprise Guide Explorer >

In Enterprise Guide Explorer

Tools > Options > Uncheck Enable Task Administration

SAS EG and .NET 2.0

Ok, so the official word is no .NET 2.0 apps in EG. I understand this position 100% and I agree with the position. Regardless, .NET 2.0 costs me 25-50% less effort than 1.1 so my goal was to see if I could hack out something that would allow me to post a 2.0 app in EG 4.1.

It is a hack, it's not official, it's limited, etc. but I successfully got my 2.0 app to run under EG and had it post my code to an EG task. Here's how I did it but it is simplistic and not pretty. I share it in case you need something similar.

First, create a 2.0 app. Make it a WinForm and have fun on layout, generics, etc.

Then change parts of your program.cs to something like the following:

MainForm frm = new MainForm();
Application.Run(frm);
Console.WriteLine(frm.SasCode);

All Winform apps can write to a console but this output goes to a standard out.

Then change your EG add-in to support it:

public SAS.Shared.AddIns.ShowResult Show(System.Windows.Forms.IWin32Window Owner)
{
Process proc ;
proc = new Process() ;
proc.StartInfo.UseShellExecute = false ;
proc.StartInfo.RedirectStandardOutput = true ;
proc.StartInfo.RedirectStandardError = true ;
proc.StartInfo.CreateNoWindow = true ;
proc.StartInfo.FileName = "AnalystToolkit.exe";
proc.Start() ;
proc.WaitForExit() ;
sasCode = proc.StandardOutput.ReadToEnd() ;
return SAS.Shared.AddIns.ShowResult.RunLater;
}

I could have done a lot more with standard out (and I probably will) but this shows you a quick and easy way to hack up a solution that works. From this standard out, you should be able to make out a way to do anything you need.


From out in left field and having fun,

Alan