Execute SAS code sent through PHP

Reply
Occasional Contributor
Posts: 5

Execute SAS code sent through PHP

Hi,

We are currently working on a small project where we are trying to execute code on our SAS server and return the result to our PHP server to display the data on a website.

We managed to connect to the SAS server and are able to execute SQL code using the following lines:

$con = new COM("ADODB.Connection");

$conn->Open("Provider=SAS.IOMProvider.9.4;Data Source=iom-bridge://<<ip and credentials>>");

$testquery = $conn->Execute("libname SASHELP '<<path>>'");

$testquery = $conn->Execute("SELECT * FROM SASHELP.class");

Now we would like to use something like this:

proc print data=SASHELP.class;

Simply changing $testquery to the above saddly doesn't work due to the following error:

SQL passthru expression contained these errors: ERROR 180-322: Statement is not valid or it is used out of proper order.

What is the correct way to send such code to the SAS server?

Thanks in advance

Super User
Posts: 10,023

Re: Execute SAS code sent through PHP

proc print data=SASHELP.class; run;

Occasional Contributor
Posts: 5

Re: Execute SAS code sent through PHP

Already tried putting that in the Execute procedure but it does not work.

The problem is not the SAS code, but the method to send the code over to the SAS server using PHP

Super User
Posts: 7,768

Re: Execute SAS code sent through PHP

ADODB is a database abstraction layer, so don't expect anything but code valid in SAS SQL to work there.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: Execute SAS code sent through PHP

Posted in reply to KurtBremser

I would agree, and the line with the libname statement would also not work.  Have a read up on SQL passthough:SAS(R) 9.2 Language Reference: Concepts, Second Edition

Super User
Posts: 7,768

Re: Execute SAS code sent through PHP

libname is one of the "global" SAS statements that basically works "everywhere", so it does not throw an error and works if executed inside a proc sql step. I just checked this with:

proc sql;

libname library 'some_path';

select * from library.test;

quit;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Execute SAS code sent through PHP

Posted in reply to KurtBremser

So is there no other method to send SAS code from PHP to the SAS server and then get data back in PHP?

If not we will have to rethink our strategy.

Super User
Super User
Posts: 7,942

Re: Execute SAS code sent through PHP

I am not sure I understand your requirements here.  PHP is scripting for the server, why would that be generating SAS code?  Would you not want to send "parameters" along to a stored process on the SAS server?

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

Occasional Contributor
Posts: 5

Re: Execute SAS code sent through PHP

What we want to do is have a website with very basic SAS exercises.

A user will have to put something along the lines of "proc print data=SASHELP.class; run;" if that is what the exercise demands.

We then want that input go to the SAS server and then get the result back.

Please note we do NOT want to just compare input code with the solution code

Super User
Super User
Posts: 7,942

Re: Execute SAS code sent through PHP

You would be best off speaking to SAS about what you can do there then.  That's not so much a small question, but a system setup with licensing issues and other items. 

Occasional Contributor
Posts: 5

Re: Execute SAS code sent through PHP

We actually have the approval and help of SAS Belgium.

We also have decided to maybe ditch the idea of communicating using PHP and instead seek other ways like Java.

Super User
Posts: 7,768

Re: Execute SAS code sent through PHP

Have a thorough look at the stored process server. You could write a STP that gets code as a parameter and executes that with call execute. Another method would be to write a cgi wrapper that stores the "exercise code" as a file, executes SAS with that in batch mode, and delivers the result(s) (output, log) back in HTML form.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 3,212

Re: Execute SAS code sent through PHP

Would check: SAS Integration Technologies with SAS(R) 9.4 Integration Technologies: Java Client Developer's Guide
Maybe PHP support the same calls. As soon as you have a connection getting data out of that will become the layout/structure important.

---->-- ja karman --<-----
Trusted Advisor
Posts: 1,301

Re: Execute SAS code sent through PHP

SasAdoDbExample.php

<?php

$conn = new COM("ADODB.Connection");

$conn->Open('Provider=sas.IOMProvider.9.3;User ID=user;Password=pass;Data Source="";SAS Machine DNS Name=my.server.com;SAS Port=8591;SAS Protocol=2');

$cmd = new COM("ADODB.Command");

$cmd->ActiveConnection = $conn;

$cmd->CommandType = 1;

$cmd->CommandText = 'libname foo "/my/path"';

$cmd->Execute();

$recordSet = $conn->Execute("select * from foo.my_table");

$recordSet->MoveFirst();

do {

   for ($i=0; $i < $recordSet->Fields->Count; $i++) {

   echo $recordSet->Fields->Item($i)->Name . ": " . $recordSet->Fields->Item($i)->Value . "\n";

  }

   echo "\n\n";

   $recordSet->MoveNext();

} while ($recordSet->EOF == FALSE);

$recordSet->Close();

$conn->Close();

?>

Ask a Question
Discussion stats
  • 13 replies
  • 685 views
  • 3 likes
  • 6 in conversation