topic Re: How to create PROC SQL with conditional macro variables? in SAS Programming

How to create PROC SQL with conditional macro variables?

afiqcjohari — Tue, 14 Mar 2017 08:27:35 GMT

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

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

gamotte — Tue, 14 Mar 2017 08:33:39 GMT

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;

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

gamotte — Tue, 14 Mar 2017 08:38:38 GMT

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

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

andreas_lds — Tue, 14 Mar 2017 08:46:41 GMT

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

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

gamotte — Tue, 14 Mar 2017 08:48:10 GMT

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

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

afiqcjohari — Tue, 14 Mar 2017 08:48:31 GMT

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

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

gamotte — Tue, 14 Mar 2017 08:55:32 GMT

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

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

afiqcjohari — Tue, 14 Mar 2017 09:06:06 GMT

Got it.

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

afiqcjohari — Tue, 14 Mar 2017 09:06:48 GMT

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.

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

Kurt_Bremser — Tue, 14 Mar 2017 09:18:47 GMT

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

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

afiqcjohari — Tue, 14 Mar 2017 09:21:01 GMT

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

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

Kurt_Bremser — Tue, 14 Mar 2017 09:25:52 GMT

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

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

RW9 — Tue, 14 Mar 2017 09:26:13 GMT

What is it your actually trying to achieve here. The reason I ask is that, firstly that code does not add any value to just a base SQL query (i.e. the macro code is totally redundant here), and secondly using the same paramter for a dataset name and where, both of which have very different restrictions on whwta can be put there, will make that code fall over every time it is run. Not a good way to write code.

Now you mention later that you want to put dates in dataset names, I always treat this as a bad idea for several reasons:

First, you then need to know what the dataset names are - i.e. they are not fixed, so this makes programming more complex than necessary.

Secondly, you often want to do calculations on dates, if these appear in the dataset name, you then have to get that, convert it, do the calculations, then convert it back again. All of this again exponentiates the necessary code/maintenance.

So lets say, I need to extract 01jan2014 and 05feb2015 and print them, I could go through the hassel of creating a separate dataset for each, putting date in the name, then doing a mess of macro code to loop over each of the datasets, or I could just do:

data want;
  set have (where=(date in ("01JAN2014"d,"05FEB2015"d)));
run;
proc print data=want;
  by date;
  title "#byval1";
run;

Which code woul you prefer to maintain?

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

afiqcjohari — Tue, 14 Mar 2017 09:29:23 GMT

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.

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

afiqcjohari — Tue, 14 Mar 2017 09:30:55 GMT

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;

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

Kurt_Bremser — Tue, 14 Mar 2017 09:34:18 GMT

@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

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

afiqcjohari — Tue, 14 Mar 2017 09:38:24 GMT

The table above is what I'm trying to achieve. Basically it's just a simple pivot table with the months on the column and some variables on the left. Note that some of the variables can be broken down. For example, variable Spend can be broken into LCY and FCY.

The reason for MACRO is that, I'd need to recreate the same table for another `types`.

You can imagine that above table is about FRUITS in general. I'd like then to recreate the same table but for APPLE.

So far, I've been using PROC SQL to calculate the sum(), count(), etc and then use PROC TRANSPOSE to get the time series or pivot table as above.

Though, I like to believe that SAS would have somthing more simpler to generate this kind of report :). Would be very glad if you know how.

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

RW9 — Tue, 14 Mar 2017 10:11:26 GMT

Ah, so your imitating some Excel output. Ok, then yes having a macro variable, or macro call is fine, but you don't need to change the internal dataset then:

%macro report (type=);
  
  data want;
    set have (where=(type="&type."));
  run;
 
  proc transpose data=want out=tran_want;
    ...;
  run;

  proc report data=tran_want...;
  run;

%mend report;

%report (type=Apple);
%report (type=Pear);