topic Re: How to create a SAS datasets from store procedure from database in SAS Programming

How to create a SAS datasets from store procedure from database

tekish — Mon, 24 Jul 2017 16:03:06 GMT

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

Re: How to create a SAS datasets from store procedure from database

LinusH — Fri, 21 Jul 2017 19:35:38 GMT

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.

Re: How to create a SAS datasets from store procedure from database

Quentin — Fri, 21 Jul 2017 20:08:23 GMT

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.

Re: How to create a SAS datasets from store procedure from database

tekish — Fri, 21 Jul 2017 23:10:30 GMT

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.

Re: How to create a SAS datasets from store procedure from database

tekish — Mon, 24 Jul 2017 19:08:26 GMT

Here is the solution for my store procedure.

%macro test;

options msglevel=i nodate noquotelenmax mprint;

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

%mend test;