DATA Step, Macro, Functions and more

How to create a SAS datasets from store procedure from database

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

How to create a SAS datasets from store procedure from database

[ Edited ]

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


Accepted Solutions
Solution
‎07-24-2017 03:08 PM
Contributor
Posts: 58

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

 

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;

View solution in original post


All Replies
Super User
Posts: 5,437

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

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.
Data never sleeps
Contributor
Posts: 58

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

;

 

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

 

 

PROC Star
Posts: 1,324

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

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.

Solution
‎07-24-2017 03:08 PM
Contributor
Posts: 58

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

 

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 112 views
  • 0 likes
  • 3 in conversation