BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tekish
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
tekish
Quartz | Level 8

 

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

4 REPLIES 4
LinusH
Tourmaline | Level 20
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
tekish
Quartz | Level 8

;

 

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

 

 

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
tekish
Quartz | Level 8

 

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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