CODEWITHSUNDEEP
sasmacrossas-macrodo-loopsmacro-variable
MLPNPC
MLPNPC

Reputation: 525

SAS Macro Variables

I am trying to create a macro that needs to reference 2 dates, I want this to be part of a do loop so I don't have to enter dates in 36 times.

Here's what I have so far:

PROC SQL NOPRINT;
SELECT MMMYY INTO : mmmyy1 -
FROM USE_DATES;
%LET T=&SQLOBS.;
QUIT;

PROC SQL NOPRINT;
SELECT _YEAR_ INTO : yy1 -
FROM USE_DATES;
%LET T=&SQLOBS.;
QUIT;

PROC SQL NOPRINT;
SELECT mm INTO : mm1 -
FROM USE_DATES;
%LET T=&SQLOBS.;
QUIT;


%MACRO MATS(DATE, NUM);
%let j=%eval(&i+1);
%let month = &mm&&j.;
%let year_x = &yy&&j.;

PROC SQL;
CREATE TABLE TEST AS 
SELECT 
PRODUCT_EXPIRY_DATE, 
COUNT(*)
FROM DATA.AGGREGATE_ACCOUNTS_&mmmyy&&i.
WHERE YEAR(PRODUCT_EXPIRY_DATE) = &year_x. AND MONTH(PRODUCT_EXPIRY_DATE) = &month.

;
QUIT;
%MEND;

%MACRO apply_MATS;
%DO i = 1 %to 1  %by 1;
%MATS(&mmmyy&&i.,&i.);
%end;
%mend;

%apply_MATS;

The problem is that it doesn't assign year_x so the code fails with the error code:

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 200-322: The symbol is not recognized and will be ignored.

I can't work out what I've done wrong? Is anyone able to help?

Upvotes: 0

Views: 78

Answers (2)

Richard
Richard

Reputation: 27508

You DO NOT NEED MACRO.

You can be better served by learning more about reporting procedures, date formats, where clauses and by group processing.

Example:

Would a report suffice?

data have;
  call streaminit(2024);
  do xid = 1 to 1e5;
    date = today() - rand('integer', 5000) ;
    output;
  end;
  format date yymmdd10.;
run;
data for_report;
  set have;
  year = year(date);
  month = intnx('month', 0, month(date)-1);
  m = put(month, monname.);
run;
ods html file='r.html' style=plateau;
proc tabulate data=for_report;
  class year / descending;
  class month / order=internal; 
  format month monname3.;
  table month, year*n;
  where intck('MONTH', date, today()) <= 36;
run;
ods html close;

enter image description here

Upvotes: -1

Tom
Tom

Reputation: 51566

You are not using the && in the right place. If &J=1 and want the value of YY1 then you should do:

%let year_x = &&yy&j;

The first pass will convert && into & and &J into 1 resulting in &yy1. Which the second pass will convert to the value of YY1.

But you are making it much too hard. If the goal is to select by YEAR and MONTH then construct the inner macro in that way.

%MACRO MATS(year, month, NUM);
PROC SQL;
CREATE TABLE TEST&num. AS 
  SELECT PRODUCT_EXPIRY_DATE
       , COUNT(*)
  FROM DATA.AGGREGATE_ACCOUNTS_&year.&month.
  WHERE YEAR(PRODUCT_EXPIRY_DATE) = &year
    AND MONTH(PRODUCT_EXPIRY_DATE) = &month.
;
QUIT;
%MEND;

Then you can call the macro once for each combination of MM and YY.

data _null_;
   set USE_DATES;
   call execute(cats('%nrstr(%MATS)(',yy,',',mm,',',_n_,')'));
run;

Upvotes: 1

Related Questions