topic Re: Macros for overall and single value in SAS Programming

Macros for overall and single value

Dipu — Tue, 04 Feb 2014 16:35:09 GMT

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

Re: Macros for overall and single value

Amir — Tue, 04 Feb 2014 17:51:41 GMT

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.

Re: Macros for overall and single value

Astounding — Tue, 04 Feb 2014 19:55:25 GMT

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.

Re: Macros for overall and single value

Dipu — Tue, 04 Feb 2014 19:58:54 GMT

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

Re: Macros for overall and single value

Dipu — Tue, 04 Feb 2014 20:13:46 GMT

Thank you @ Astounding for your reply.