How to create PROC SQL with conditional macro variables?

[RW9]

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?

[afiqcjohari]

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.

[RW9]

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