topic Re: Macro Mess with beginning and ending multiple macros in SAS Procedures

Macro Mess with beginning and ending multiple macros

pangea17 — Tue, 16 Jan 2018 19:49:08 GMT

I am having a problem setting up the beginning and ending of a couple of macros I am trying to run in a SAS job. I am using enterprise guide 7.15 and SAS version 9.x I have tried moving the history mend statement to the beginning of the program after the data null step and calling this macro

%macro history( ); within the cleanup macro, but that did not work either. Any help would be appreciated.

Re: Macro Mess with beginning and ending multiple macros

ballardw — Tue, 16 Jan 2018 20:01:32 GMT

Please post code into a code box opened with either the {I} or running man forum icons.

Microsoft Word documents are known to have issues with such things as macro viruses and many folks on this forum either won't open such or cannot due to organizational policy or nanny-ware.

Word has been known to "fix" code by changing programming quotes, the straight up and down types, to "smart quotes", the "prettier" curly versions, which yields code that will not run. Also other formatting changes may be made that insert invisible characters also resulting in code that will not run.

Re: Macro Mess with beginning and ending multiple macros

Astounding — Tue, 16 Jan 2018 21:11:32 GMT

Being one of those who won't open a Word document, I can only guess at the problem. Here's what I would change.

Define each macro separately from every other macro. There is hardly ever a reason that a macro should be defined within another macro.

You can still call one macro from another. For example, the command %A can appear within the definition of %B. But the statement %macro A should almost never appear within the definition of %B.

That may not fix all the problems, but it will clarify the structure of the macros, and what is supposed to happen at what point.

Re: Macro Mess with beginning and ending multiple macros

pangea17 — Wed, 17 Jan 2018 01:20:58 GMT

libname mccoy '\\lassasc01\temp\collection\';

%LET Z = '01jan1900'd;

%macro history (d1, d2, d3, d4, d5);

data _null_;

call symputx('A', PUT(intnx('YEAR',%SYSFUNC(today()),-6), date9.)); * 6 years ago;

call symputx('B', PUT(intnx('YEAR',%SYSFUNC(today()),-5), date9.)); * 5 years ago;

call symputx('C', PUT(intnx('YEAR',%SYSFUNC(today()),-4), date9.)); * 4 years ago;

call symputx('D', PUT(intnx('YEAR',%SYSFUNC(today()),-3), date9.)); * 3 years ago;

call symputx('E', PUT(intnx('YEAR',%SYSFUNC(today()),-2), date9.)); * 2 years ago;

call symputx('F', PUT(intnx('YEAR',%SYSFUNC(today()),-1), date9.)); * 1 year ago;

call symputx('G', PUT(intnx('YEAR',%SYSFUNC(today()),0), date9.)); * jan current year ago;

call symputx('H', PUT(intnx('YEAR',%SYSFUNC(today()),0,'e'), date9.)); * dec current year ago;

call symputx('CBEG', PUT(intnx('MONTH',%SYSFUNC(today()),&d1,'b'), date9.)); *last mo beginning date;

call symputx('CEND', PUT(intnx('MONTH',%SYSFUNC(today()),&d2,'e'), date9.)); *last mo ending date;

call symputx('PBEG', PUT(intnx('MONTH',%SYSFUNC(today()),&d3,'b'), date9.)); *2 mos ago beginning date;

call symputx('PEND', PUT(intnx('MONTH',%SYSFUNC(today()),&d4,'e'), date9.)); *2 mos ago ending date;

call symputx('Q', PUT(intnx('MONTH',intnx('YEAR',%SYSFUNC(today()),-1,'b'),11,'e'), date9.)); * Dec prior year;

call symputx('J', PUT(intnx('MONTH',%SYSFUNC(today()),-85,'e'), date9.));

RUN;

%macro cleanup(bodate, eodate, wstart, wend, istart, iend, s);

data _null_;

call symputx('M', PUT(intnx('MONTH',%SYSFUNC(today()),&s,'e'), date9.));

RUN;

proc sql;

create table was as

select

a.cardid, a.creditaccountid, a.opendate,

datepart(a.opendate) as odate format = mmddyy10., a.activeaccountindicator,

a.endingreceivable as er_was, a.dimproductid, c.offer,

a.dimexternalstatusid as es_was, a.dimbucketid as b_was, b.originalcreditline,

d.score as cbscore, d.creditbureauscorerange, e.score as bhscore,

e.behaviorscorerange

from

newdw.factfinancialcyclev2 a, newdw.dimproduct b, newdw.dimproductoffered c,

newdw.dimscore d, newdw.dimscore e

where

(a.reportingdate ge &wstart and a.reportingdate le &wend)

and a.activeaccountindicator = 1

and a.opendate ge &bodate

and a.opendate le &eodate

and a.dimproductid = b.dimproductid

and a.dimproductofferedid = c.dimproductofferedid

and a.dimcreditbureauscoreid = d.dimscoreid

and a.dimbehaviorscoreid = e.dimscoreid

and a.dimexternalstatusid in (3, 5, 10); *C, F and Blank external status codes;

Quit;

proc sort data = was; by creditaccountid; run;

proc sql;

create table is as

select

a.cardid, a.creditaccountid, a.chargeoffindicator,

a.endingreceivable as er_is, a.dimexternalstatusid as es_is,

a.dimbucketid as b_is

from

newdw.factfinancialcyclev2 a

where

(a.reportingdate ge &Istart and a.reportingdate le &Iend)

and a.activeaccountIndicator =1;

QUIT;

data is;

set is;

if chargeoffindicator = 1 then b_is = 210;

run;

proc sort data = is; by creditaccountid; run;

data was_isa was_isb;

merge was (in = a) is (in = b);

by creditaccountid;

if a and b then output was_isa;

if a and not b then output was_isb;

run;

data was_is;

set was_isa was_isb;

format open_date mmddyy10.;

if es_was in (7, 8) or es_is in (7, 8) then delete; /*lost and stolen*/

if b_is = . then delete;

open_date = datepart(opendate);

if open_date lt "&A"d then newdate = substr("&A",6,4)-1;

if open_date ge "&A"d and open_date le "&B"d then newdate = substr("&A",6,4);

if open_date ge "&B"d and open_date le "&C"d then newdate = substr("&B",6,4);

if open_date ge "&C"d and open_date le "&D"d then newdate = substr("&C",6,4);

if open_date ge "&D"d and open_date le "&E"d then newdate = substr("&D",6,4);

if open_date ge "&E"d and open_date le "&F"d then newdate = substr("&E",6,4);

if open_date ge "&F"d and open_date le "&G"d then newdate = substr("&F",6,4);

if open_date ge "&G"d and open_date le "&H"d then newdate = substr("&G",6,4);

run;

proc sort data = was_is; by newdate b_was b_is; run;

proc means n median sum noprint data = was_is;

var b_was B_is;

by newdate b_was b_is;

output out = was_is_result

n(b_was) = Accounts

sum(er_was) = Bal_Was

sum(er_is) = Bal_Is

median(cbscore) = CBureau

median(bhscore) = BH_Score;

run;

data was_is_result2 (drop=_type_ _freq_);

format Ctype $7. monthdate mmyyd7.;

set was_is_result ;

Ctype = 'Vintage';

Monthdate = "&M"d;

label Accounts="Accounts";

label cbureau ="CBureau";

label BH_Score ="BH_Score";

run;

proc sql;

create table vintrb AS

Select monthdate, newdate, b_was, sum(accounts) as taccounts

from was_is_result2

group by monthdate, newdate, b_was;

QUIT;

proc sql;

create table vintday AS

select a.*, b.taccounts

from work.was_is_result2 a left join work.vintrb b

ON a.b_was = b.b_was

AND a.monthdate=b.monthdate

AND a.newdate=b.newdate;

QUIT;

data newvint;

format rollrate percent8.2;

set vintday;

rollrate = (accounts/taccounts);

RUN;

data VintExport;

retain b_was b_is accounts rollrate bal_was bal_is CBureau bh_score monthdate ctype newdate;

set newvint;

keep b_was b_is accounts rollrate bal_was bal_is CBureau bh_score monthdate ctype newdate;

RUN;

proc sort data= VintExport; by monthdate newdate b_was b_is; RUN;

/*data mccoy.vintage;

set VintExport;

RUN;*/

proc append base=mccoy.Vintage data=VintExport;

%mend cleanup;

%cleanup ("&Z"d, "&Q"d, "&PBEG"d, "&PEND"d, "&CBEG"d, "&CEND"d, &d5);

%MEND;

%history (-1,-1,-2,-2,-1);

So it's not able to recognize &d5 in clean up macro as the -1 in the %history macro.

Re: Macro Mess with beginning and ending multiple macros

Quentin — Wed, 17 Jan 2018 01:55:42 GMT

Hi,

That's a lot of code, and I didn't read through it. What is the problem you are seeing? It sounds like you believe it's a problem with passing parameters between the macros? Here is a simplified shell of your code, it works:

%macro history (d1, d2, d3, d4, d5);
  %put Inside History ;
  %put _local_ ;

  %macro cleanup(bodate, eodate, wstart, wend, istart, iend, s);
    %put Inside Cleanup ;
    %put _local_ ;
  %mend cleanup;

  %cleanup (0, 0, 0, 0, 0, 0, &d5)

%MEND; 

%history (1, 1, 1, 1, 5)

It returns:

41   %history (1, 1, 1, 1, 5)
Inside History
HISTORY D1 1
HISTORY D2 1
HISTORY D3 1
HISTORY D4 1
HISTORY D5 5
Inside Cleanup
CLEANUP BODATE 0
CLEANUP EODATE 0
CLEANUP IEND 0
CLEANUP ISTART 0
CLEANUP S 5
CLEANUP WEND 0
CLEANUP WSTART 0

As Astounding wrote, it's a bad idea to have nested macro definitions. You can call a macro from another macro without nesting the definitions themselves. So the below will give the same results, and is easier to manage:

%macro history (d1, d2, d3, d4, d5);
  %put Inside History ;
  %put _local_ ;

  %cleanup (0, 0, 0, 0, 0, 0, &d5)
%mend history ;
 
%macro cleanup(bodate, eodate, wstart, wend, istart, iend, s);
  %put Inside Cleanup ;
  %put _local_ ;
%mend cleanup;

%history (1, 1, 1, 1, 5)

Re: Macro Mess with beginning and ending multiple macros

Tom — Wed, 17 Jan 2018 04:09:32 GMT

The error message at the bottom of your WORD file is talking about number of macro parameters.

ERROR: More positional parameters found than defined.

Looking at your %MACRO statements it looks like all of your macro parameters are defined to support being called by position and your calls appear to have the right number of commas. Perhaps there are syntax errors in your macro definitions so that they are not getting defined and you end up calling some older version of the macro with different parameters? Perhaps your macro variables used in the macro calls have commas in them? Or perhaps some other statement is generating that error message?

Don't nest macro definitions. It will just confuse you.

%macro cleanup...;.....%mend cleanup;
%macro history...; ...%cleanup..... %mend history;
%history(....)

Don't use %sysfunc() to call functions when you are running a data step!! Just call the function directly!

data _null_;
  call symputx('A', PUT(intnx('YEAR',today(),-6), date9.));    * 6 years ago;
  call symputx('B', PUT(intnx('YEAR',today(),-5), date9.));    * 5 years ago;
  call symputx('C', PUT(intnx('YEAR',today(),-4), date9.));    * 4 years ago;
  call symputx('D', PUT(intnx('YEAR',today(),-3), date9.));    * 3 years ago;
  call symputx('E', PUT(intnx('YEAR',today(),-2), date9.));    * 2 years ago;
  call symputx('F', PUT(intnx('YEAR',today(),-1), date9.));    * 1 year ago;
  call symputx('G', PUT(intnx('YEAR',today(),0), date9.));     * jan current year ago;
  call symputx('H', PUT(intnx('YEAR',today(),0,'e'), date9.)); * dec current year ago;
  call symputx('CBEG', PUT(intnx('MONTH',today(),&d1,'b'), date9.)); *last mo beginning date;
  call symputx('CEND', PUT(intnx('MONTH',today(),&d2,'e'), date9.)); *last mo ending date;
  call symputx('PBEG', PUT(intnx('MONTH',today(),&d3,'b'), date9.)); *2 mos ago beginning date;
  call symputx('PEND', PUT(intnx('MONTH',today(),&d4,'e'), date9.)); *2 mos ago ending date;
  call symputx('Q', PUT(intnx('MONTH',intnx('YEAR',today(),-1,'b'),11,'e'), date9.));  * Dec prior year;
  call symputx('J', PUT(intnx('MONTH',today(),-85,'e'), date9.));
RUN;

Where do you define the value for macro variable Z that you use in the call to the CLEANUP macro?

%cleanup ("&Z"d, "&Q"d, "&PBEG"d, "&PEND"d, "&CBEG"d, "&CEND"d, &d5);

Re: Macro Mess with beginning and ending multiple macros

ballardw — Wed, 17 Jan 2018 16:13:24 GMT

If people aren't going to spend a lot of time reading your macro generated code then you can cause a lot of issues with

statements usages like

call symputx('A', PUT(intnx('YEAR',%SYSFUNC(today()),-6), date9.)); * 6 years ago;

and

if open_date lt "&A"d then newdate = substr("&A",6,4)-1;

just create the numeric values and use the proper functions for dealing with dates such as

(%sysfunc is not needed as you are doing this in a data step, calling a data step function TODAY())

call symputx('A', intnx('YEAR',today(),-6); * 6 years ago;

if open_date lt &A then newdate = year(&a)-1;

if  &A le open_date le &B then newdate = year(&A);

assuming all of your A, B, C etc variables are made as in the example for A. Though I suspect that the way you create and use the A through H variables you could likely get by with a single if/then and appropriate use of the year function on open_date.

I will say that calling a variable "newdate" when the content is a year has the potential for some confusion.