How to create PROC SQL with conditional macro variables?

[afiqcjohari]

%macro extract(condition=);
   proc sql;
         create table calculatedTable&condition. as
         select count(*) from table
         if &condition. is not null then where var = condition;
   quit;
%mend extract;

*the following should run proc sql count * with the condition;
%extract(condition='a');

*the following should just run a simple sql select count *  ;
%extract();

The previous calls should give 2 tables in the end

The 'where' statement should only be available if the parametre condition is supplied with a value.

Is it possible to do this?

I have a series of SQL statements which have a table as an output.

If there's no condition supplied, the SQL statements will have no 'where' condition.

However, if a condition is supplied, the same series of SQL statements will run which will have a table as an ouput.

Naturally macro should be a solution but I can't get my head around macro and proc sql to work.

[gamotte]

Hello,

You have to use the macro language %if.

%macro extract(condition=);
   proc sql;
         create table calculatedTable&condition. as
         select count(*) from table
         %if &condition. ne %then %do;
               where var = &condition.;
         %end;
   quit;
%mend extract;

[gamotte]

Note that if you have several calls with no condition, the same output dataset name will be used and will thus be overwritten. You may want to add a suffix parameter to your macro.

You also made a mistake in the macro call

%extract(condition='a');

should be

%extract(condition=a);

[andreas_lds]

---

@gamotte wrote:

Note that if you have several calls with no condition, the same output dataset name will be used and will thus be overwritten. You may want to add a suffix parameter to your macro.

You also made a mistake in the macro call

%extract(condition='a');

should be

%extract(condition=a);

---

Problem: the quotes are necessary if var is alphanumeric, so appending a dequoted version of condition to the dataset-name is recommended.

[gamotte]

You're right. I thought "a" was the variable name,

[afiqcjohari]

What's the best tips for if the condition here is a date variable.

I want to pass 01JAN2017 as the starting date for example.

[Kurt_Bremser]

---

@afiqcjohari wrote:
What's the best tips for if the condition here is a date variable.

I want to pass 01JAN2017 as the starting date for example.

---

Depends on how you aquire that date. If you write it literally in your program, you can use the normal method

extract(condition='01jan2017'd)

If you determine it from some other values, you can pass it as the raw numerical value:

%startdate=%sysfunc(today());

%extract(condition=&startdate)

or you set it from a data step:

data _null_;
set infile;
where /* condition */;
call symput('startdate',put(startdate,best.));
run;

[afiqcjohari]

I keep getting 'Apparent symbolic reference not resolved' if I literally put '01jan2017'd as the date variable.

[Kurt_Bremser]

---

@afiqcjohari wrote:
I keep getting 'Apparent symbolic reference not resolved' if I literally put '01jan2017'd as the date variable.

---

Please post the log, including the macro definition and the macro call.

[afiqcjohari]

I caught it. It's because i'm trying to run the macro in rsubmit. I've written it so that the SAS server can read the macro.

[afiqcjohari]

Basically it ends up like this. But it makes me wonder if the rsubmit is a bit redundant..

rsubmit;
%macro test(mydate=);
proc sql outobs = 10;
create table test as
select * from tablea where
dTran = &mydate.
;quit;
%mend test;
endrsubmit;
rsubmit;
%test(mydate='01jan2017'd);
endrsubmit;

[Kurt_Bremser]

---

@afiqcjohari wrote:
Basically it ends up like this. But it makes me wonder if the rsubmit is a bit redundant..

rsubmit;
%macro test(mydate=);
proc sql outobs = 10;
create table test as
select * from tablea where
dTran = &mydate.
;quit;
%mend test;
endrsubmit;
rsubmit;
%test(mydate='01jan2017'd);
endrsubmit;

---

rsubmit;

%macro test(mydate=);
proc sql outobs = 10;
create table test as
select * from tablea
where dTran = &mydate.
;
quit;
%mend test;

%test(mydate='01jan2017'd);

endrsubmit; 

should also work

[afiqcjohari]

calculatedTable&condition. should cover the overwriting issue right? Could you explain what do you mean by suffix parameter here?

[gamotte]

I answered a bit too fast. Since your input dataset is always the same, there is no use to call

the macro twice with no condition. If you had an additional parameter for the input table,

you would have to make sure that two distincts calls give distincts output datasets names,

either by naming the output dataset according to the one given in input, or by adding a custom

suffix when no condition is given

[afiqcjohari]

Got it.