DATA Step, Macro, Functions and more

How to create PROC SQL with conditional macro variables?

Reply
Frequent Contributor
Posts: 99

How to create PROC SQL with conditional macro variables?

[ Edited ]
%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.

Regular Contributor
Posts: 233

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to afiqcjohari

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;
Regular Contributor
Posts: 233

Re: How to create PROC SQL with conditional macro variables?

[ Edited ]

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);

 

Super Contributor
Posts: 345

Re: How to create PROC SQL with conditional macro variables?

[ Edited ]

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.

Regular Contributor
Posts: 233

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to andreas_lds
You're right. I thought "a" was the variable name,
Frequent Contributor
Posts: 99

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to andreas_lds
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.
Super User
Posts: 7,782

Re: How to create PROC SQL with conditional macro variables?

[ Edited ]
Posted in reply to afiqcjohari

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 99

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to KurtBremser
I keep getting 'Apparent symbolic reference not resolved' if I literally put '01jan2017'd as the date variable.
Super User
Posts: 7,782

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to afiqcjohari

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 99

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to KurtBremser
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.
Frequent Contributor
Posts: 99

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to KurtBremser
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;
Super User
Posts: 7,782

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to afiqcjohari

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 99

Re: How to create PROC SQL with conditional macro variables?

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

Re: How to create PROC SQL with conditional macro variables?

Posted in reply to afiqcjohari

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

Frequent Contributor
Posts: 99

Re: How to create PROC SQL with conditional macro variables?

Got it.
Ask a Question
Discussion stats
  • 17 replies
  • 199 views
  • 0 likes
  • 5 in conversation