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.
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.
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.