Hello all,
Please have a look on this-
I have written one sql code and applied macros on it-
The Macro looks like-
%Macro Result (Cod, Report);
proc sql;
connect to oracle (user=xxxx password=xxxx path='xxx');
create table One as select * from connection to oracle
( select p1, p2, p3, Code
from Two
where code= &Cod;
disconnect from oracle;
quit;
data &report;
set one;
.
.
.
.
run;
%Mend;
% Result (Code = , Report = Rpt1_overall);
% Result (Code = 23 , Report = Rpt2);
% Result (Code = 43, Report = Rpt3);
% Result (Code = 32, Report = Rpt4);
Here, I don't have problem with last three macros but for the fist macro I don't want to define any code (value). in the proc sql step, I just want to select code, no 'where' condition.
So how can I do so?
Hope you understand this query.
Regards
Dipu
Hi,
Perhaps try substituting the whole where clause, so instead of:
where code= &Cod
you could use:
&Cod
Then you could invoke the macro as follows:
% Result (, Report = Rpt1_overall);
% Result (where Code = 23 , Report = Rpt2);
% Result (where Code = 43, Report = Rpt3);
% Result (where Code = 32, Report = Rpt4);
Regards,
Amir.
Hi,
Perhaps try substituting the whole where clause, so instead of:
where code= &Cod
you could use:
&Cod
Then you could invoke the macro as follows:
% Result (, Report = Rpt1_overall);
% Result (where Code = 23 , Report = Rpt2);
% Result (where Code = 43, Report = Rpt3);
% Result (where Code = 32, Report = Rpt4);
Regards,
Amir.
Thank you Amir for your reply.
Actually it worked, I was just worried since I had various 'and' conditions so I did the same as you mentioned, for 'and'.
Thank you
Dipu
Here's another possibility you could try. You currently have:
from Two
where cod=&cod
;
Try replacing that with:
from Two
%if %length(&cod) %then where cod=&cod;
;
Yes, you do need both semicolons. Good luck.
Thank you @ Astounding for your reply.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.