How to create a SAS datasets from store procedure from database

[tekish]

Dear Experts,

options msglevel=i nodate noquotelenmax mprint;

proc sql stimer;
  connect to oledb (provider     = 'XXXXXXXX'
                            datasource = "XXXXXXXX"
                            user       = XXXXXXXXXXX
                            password   = XXXXXXXXXXX
                            properties = ('initial catalog'=XXXXXXXXX)
                           dbmax_text  = 30000);

  create table TempData as
  select * from connection to oledb
      ( execute Storeprocedurefromdatabse
       %if &A ne %then  &A,;
       %if &B ne %then &B,;
       %if &C ne %then &C;,
       &D) by oledb;
   disconnect from oledb;
   quit;

%mend test;

I have a store procedure in a database and i want to create a tempdata using sas.
i have four parameters to pass:
@A,@B,@c,@D,

The fourth parameter(@D) is always provided by the users but the other three parameters are selected by users.

for instance for one user:

If they select @A then @B and @c is null.
If they select @B then @A and @C is null.

when i run the sas web application i got the follwoing error.

ERROR 79-322: Expecting a ).

could you please help me how to create a dataset from the store procedure.

Thanks,

kesete

[LinusH]

1. Can you successfully use the syntax with hard coded parameter values?
2. You probably didn't paste the full code, miss the macro definition since %if is not allowed in open code. 3. Use MPRINT to mirror the code generated by the macro, usually it reveals the cause of syntax error.

[tekish]

LinusH;

i have updated my codes and it gives me the same error.

this is the web application sas.

http://sas-server name/scripts/broker.exe?_program=example.sasexample.sas&_service=&servername=&_debug=0&category=&A=CO&B=&C=&D=1

thanks.

[Quentin]

Suggest you get the code working without any macro varibles first, to make sure your syntax is correct.

Then you can move on to introducing macro variables to generate the syntax.  As mentioned, hardcoding the macro variables is helpful for testing, and MPRINT is key to to seeing the code generated.

The macro %IF statement needs to end with a semicolon, i.e.:

%if &A ne %then  &A ;

That semicolon will not become part of the query.  It is a macro language semicolon for the %IF statement.

[tekish · Accepted solution]

Here is the solution for my store procedure.

%macro test;

options msglevel=i nodate noquotelenmax mprint;

proc sql stimer;
  connect to oledb (provider     = 'XXXXXXXX'
                            datasource = "XXXXXXXX"
                            user       = XXXXXXXXXXX
                            password   = XXXXXXXXXXX
                            properties = ('initial catalog'=XXXXXXXXX)
                           dbmax_text  = 30000);

  create table TempData as
  select * from connection to oledb
      ( execute Storeprocedurefromdatabse
       &A,
      &B,
      &C,
       &D);
   disconnect from oledb;
   quit;

%mend test;