Macros for overall and single value

[Dipu]

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

[Amir · Accepted solution]

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.

[Dipu]

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

[Astounding]

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.

[Dipu]

Thank you @ Astounding for your reply.