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
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;
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.