Using the year(variable) function in %IF%THEN%ELSE

[mjabed600]

Hi all,

I have recently started learning SAS was trying to create a macro but ran into an error. I have a dataset with a variable order_date which is a numeric type in mmddyy10 format and order_type which is numeric as well with values of either 1 or 2. I am trying to write a macro where, I can calculate the frequency of orders of a specific type of order in a specific year. OR lets say I only specify a year, i should get frequency of all the types of orders from that year. Also, wanted to have appropriate titles for example, "total number of orders for 2005" or "total number of type 1 orders in 2005". I am using SAS university edition. below is the code

%macro frequency(orderyr=, type= );
%if year(order_date)= &orderyr and order_type= &type %then
      %do;
              title"Total Type &type orders for &orderyr";
              proc freq data=myexcel;
              tables order_type * Quantity/norow nocol nocum nopercent;
      %end;

%else %if year(order_date) = &orderyr %then
       %do;
               title"Total Orders for all types in &orderyr";
               proc freq data=myexcel;
               tables order_type * quantity/norow nocol nocum nopercent;
      %end;
%mend frequency;
run;

%frequency;

this is the error : 

ERROR: Required operator not found in expression: year(order_date)= &orderyr and order_type= &type

 ANy help is really appreciated. Thank you

[Astounding]

There is nothing about your program that brings in ORDER_DATE.  Is it supposed to be part of the SAS data set MYEXCEL?

In that case you might begin your macro with:

data _null_;

set myexcel;

call symputx('order_year', year(order_date));

stop;

run;

Then you could compare with:

%if &order_year = &orderyr and .......

[mjabed600]

Thank you for the reply.

[ballardw · Accepted solution]

Your first issue is that your call to the macro does not set values for either of orderyr or type macro variables. The call should look something like:

%frequency(2018, 1);

if you want to use 2018 for the orderyr and 1 for the type. When you leave them blank the %if doesn't have anything to use and you have to use some additional code (not obvious) to test whether you supplied a value at all.

The macro language doesn't actually use dataset variable values, at least not without a great deal of complexity.

You can reference the value of a macro variable though.

You might consider something like

proc freq data=myexcel;
   where year(order_date)=&orderyr. and order_type=&type.; 
   tables order_type * Quantity/norow nocol nocum nopercent;
run;

You would be better off to set a special value for the macro variables that indicates they are not actually used for the report, then you have something actually test. I would suggest a value unlikely to appear such as _donotuse_

Perhaps something like this for two of the four cases involved:

%if &orderyr. = _donotuse_ and &type.=_donotuse_ %then %do;
   title"Total Orders for all types and all years";
   proc freq data=myexcel;
      tables order_type * Quantity/norow nocol nocum nopercent;
   run;
%end;
%else %if &orderyr. = _donotuse_ and &type. ne _donotuse_ %then %do;
   title"Total Type &type orders  and all years";
   proc freq data=myexcel;
      where order_type=&type.;
      tables order_type * Quantity/norow nocol nocum nopercent;
   run;
%end;

Other two cases left as an exercise.

The macro call would might then look like %frequency(_donotuse_, 1);

Since macro programming only generates code the appropriate approach is to get a Procedure or data step working without any macro code that generates what you want. Then look at how to specify the elements used from the macro language. In this case the key element is 1) selecting  the records to analyze, which would be a where statement since you want to do only one value of one or two variables and 2) change the title.

[mjabed600]

thank you for your response. You're right, I tried to write a macro before trying to create a working proc step. Thank you once again