BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mjabed600
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
Astounding
PROC Star

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
Fluorite | Level 6
Thank you for the reply.
ballardw
Super User

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
Fluorite | Level 6
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 387 views
  • 0 likes
  • 3 in conversation