"Statement is not valid or it is used out of proper order" Error / Macro / Call Execute / Data Step

[VCucu]

I have the following macro which I call using a data STEP:

%macro htmlexport(md=,var=,curr=,prev=);

proc sql;
create table a3 as select * from sasuser.production
where branchID ="&var" and mandantid="&md" and generation in ("&curr","&prev");
quit;

%mend htmlexport;

Call execute Macro Step:

1) Prepare list of variables:

proc sql;
create table temp as select distinct mandantid, branchid, '202007' as curr, '202006' as prev from sasuser.production
where mandantid='196' and branchid<>'?';
quit;

this creates the following table:

2) Running Call Execute

data _null_;
set temp;
str=catt('%htmlexport(md=',mandantid,',var=',branchid,',curr=',curr,',prev=',prev,');');
call execute(str);
run;

However i get the following error and I don't understand what is wrong. It drives me crazy 🙂

does anybody has an idea what I am doing wrong with this call execute ?

thank you very much

[Kurt_Bremser]

Please run all three parts (macro definition, SQL, DATA step) in a single code and post the log from that. Copy/paste the log into a window opened with this button:

Such text is easier to read than pictures. Use the icon right next to it ("little running man") for SAS code.

[VCucu]

please see below log results. the first macro works (html3) while for the second (html9) i get the respective error.

1                                                          Das SAS System

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='HTML Export Level 3';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='L:\Daten\Work\SAS Production Report\HTML Export Level 3.sas';
9          %LET _SASPROGRAMFILEHOST='W0196Z01095';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS94/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         data _null_;
27          set sasuser.prodrun;
28          str=catt('%',proc,'(md=',mandantid,',var=',var,',curr=',curr,',prev=',prev,');');
29          call execute(str);
30         run;

WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: There were 25 observations read from the data set SASUSER.PRODRUN.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds
      
2                                                          Das SAS System


NOTE: CALL EXECUTE generated line.
1         + proc sql;
1         +           create table a3 as select * from sasuser.production where branchID ="DE" and mandantid="196" and generation 
in ("202007","202006");
NOTE: Table WORK.A3 created, with 1 rows and 59 columns.

1         +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           51.18 seconds
      cpu time            9.54 seconds
      
1         +
        proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,

2         + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by 
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 0 rows and 8 columns.

2         +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds
      
2         +
        proc sql; create table a3_2 as select

3         + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from 
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 1 rows and 8 columns.

3         +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
      
3         +
        proc sql;

4         + create table a3_3 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart, a3_1.dealend, 
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  
a3_2.generation as
5         + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on 
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 0 rows and 10 columns.

5         +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
      
5         +
        proc sql; create table a3_4 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart,

3                                                          Das SAS System

6         + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding 
format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left 
join
7         + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 0 rows and 10 columns.

7         +                                                       quit;
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.06 seconds
      
7         +                                                              proc sql; create table a3_5 as select  a3_2.branchid,  
      a3_2.customername,  a3_2.dealid,  a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,

8         + a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as 
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 1 rows and 10 columns.

8         +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.06 seconds
      
8         +
        proc append

9         + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run;  data a3_5;    set a3_5;    array change _numeric_;     
    do over change;             if change=. then change=0;         end;  run ;  proc sql outobs=100; create table a3_6 as select

NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 0 observations read from the data set WORK.A3_4.
NOTE: 0 observations added.
NOTE: The data set WORK.A3_5 has 1 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      


NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 0 observations read from the data set WORK.A3_3.
NOTE: 0 observations added.
NOTE: The data set WORK.A3_5 has 1 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds
      

NOTE: There were 1 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 1 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.07 seconds
      

4                                                          Das SAS System

10        + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding, 
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
NOTE: Table WORK.A3_6 created, with 1 rows and 11 columns.

10        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.15 seconds
      cpu time            0.11 seconds
      

11        + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
Report\report_196_analysis3_DE.html";  options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=DE"; footnote "top 
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
      Report\report_196_analysis3_DE.html
12        + sorted by deviation descending";  proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid; 
column (obs branchid customername dealid dealstart dealend  riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
13        + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID'; 
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product 
Name'; define
14        + previousoutstanding / display  f=comma20. "Outstanding 202006"; define currentoutstanding / display  f=comma20. 
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
15        + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed  ; compute dev; 
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run;  options byline; title2;  
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER 
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60  SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN  ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
 ) );
 
DEFINE  obs / COMPUTED FORMAT= BEST9. WIDTH=9     SPACING=2   RIGHT "obs" ;
DEFINE  BranchID / GROUP FORMAT= $3. WIDTH=3     SPACING=2   NOPRINT LEFT "BranchID" ;
DEFINE  CustomerName / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Customer Name" ;
DEFINE  DealID / DISPLAY FORMAT= $30. WIDTH=30    SPACING=2   LEFT "Deal ID" ;
DEFINE  DealStart / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal Start" ;
DEFINE  DealEnd / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal End" ;
DEFINE  RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Risk Product Name" ;
DEFINE  PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202006" ;
DEFINE  CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202007" ;
DEFINE  dev / COMPUTED FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Relative Deviation" ;
DEFINE  deviation / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Absolute Deviation" ;

COMPUTE  obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;


COMPUTE  dev;
dev = currentoutstanding - previousoutstanding;
ENDCOMP;

RUN;

NOTE: There were 1 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
5                                                          Das SAS System

      real time           0.28 seconds
      cpu time            0.04 seconds
      

16        + proc sql;
16        +           create table a3 as select * from sasuser.production where branchID ="HK" and mandantid="196" and generation 
in ("202007","202006");
NOTE: Table WORK.A3 created, with 1635 rows and 59 columns.

16        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           55.34 seconds
      cpu time            11.84 seconds
      
16        +
        proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,

17        + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by 
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 561 rows and 8 columns.

17        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.08 seconds
      cpu time            0.07 seconds
      
17        +
        proc sql; create table a3_2 as select

18        + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from 
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 521 rows and 8 columns.

18        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.12 seconds
      
18        +
        proc sql;

19        + create table a3_3 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart, a3_1.dealend, 
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  
a3_2.generation as
20        + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on 
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 494 rows and 10 columns.

20        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds
      
20        +
6                                                          Das SAS System

        proc sql; create table a3_4 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart,

21        + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding 
format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left 
join
22        + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 67 rows and 10 columns.

22        +                                                       quit;
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.04 seconds
      
22        +                                                              proc sql; create table a3_5 as select  a3_2.branchid,  
      a3_2.customername,  a3_2.dealid,  a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,

23        + a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as 
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 27 rows and 10 columns.

23        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.04 seconds
      
23        +
        proc append

24        + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run;  data a3_5;    set a3_5;    array change _numeric_;     
    do over change;             if change=. then change=0;         end;  run ;  proc sql outobs=100; create table a3_6 as select

NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 67 observations read from the data set WORK.A3_4.
NOTE: 67 observations added.
NOTE: The data set WORK.A3_5 has 94 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      


NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 494 observations read from the data set WORK.A3_3.
NOTE: 494 observations added.
NOTE: The data set WORK.A3_5 has 588 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

NOTE: There were 588 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 588 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
7                                                          Das SAS System

      

25        + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding, 
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
WARNING: Statement terminated early due to OUTOBS=100 option.
NOTE: Table WORK.A3_6 created, with 100 rows and 11 columns.

25        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.07 seconds
      

26        + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
Report\report_196_analysis3_HK.html";  options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=HK"; footnote "top 
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
      Report\report_196_analysis3_HK.html
27        + sorted by deviation descending";  proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid; 
column (obs branchid customername dealid dealstart dealend  riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
28        + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID'; 
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product 
Name'; define
29        + previousoutstanding / display  f=comma20. "Outstanding 202006"; define currentoutstanding / display  f=comma20. 
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
30        + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed  ; compute dev; 
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run;  options byline; title2;  
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER 
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60  SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN  ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
 ) );
 
DEFINE  obs / COMPUTED FORMAT= BEST9. WIDTH=9     SPACING=2   RIGHT "obs" ;
DEFINE  BranchID / GROUP FORMAT= $3. WIDTH=3     SPACING=2   NOPRINT LEFT "BranchID" ;
DEFINE  CustomerName / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Customer Name" ;
DEFINE  DealID / DISPLAY FORMAT= $30. WIDTH=30    SPACING=2   LEFT "Deal ID" ;
DEFINE  DealStart / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal Start" ;
DEFINE  DealEnd / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal End" ;
DEFINE  RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Risk Product Name" ;
DEFINE  PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202006" ;
DEFINE  CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202007" ;
DEFINE  dev / COMPUTED FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Relative Deviation" ;
DEFINE  deviation / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Absolute Deviation" ;

COMPUTE  obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;


COMPUTE  dev;
dev = currentoutstanding - previousoutstanding;
ENDCOMP;

RUN;
8                                                          Das SAS System


NOTE: There were 100 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
      real time           0.49 seconds
      cpu time            0.12 seconds
      

31        + proc sql;
31        +           create table a3 as select * from sasuser.production where branchID ="LO" and mandantid="196" and generation 
in ("202007","202006");
NOTE: Table WORK.A3 created, with 310 rows and 59 columns.

31        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           50.38 seconds
      cpu time            9.96 seconds
      
31        +
        proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,

32        + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by 
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 78 rows and 8 columns.

32        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds
      
32        +
        proc sql; create table a3_2 as select

33        + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from 
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 104 rows and 8 columns.

33        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds
      
33        +
        proc sql;

34        + create table a3_3 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart, a3_1.dealend, 
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  
a3_2.generation as
35        + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on 
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 70 rows and 10 columns.

35        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.05 seconds
9                                                          Das SAS System

      cpu time            0.04 seconds
      
35        +
        proc sql; create table a3_4 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart,

36        + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding 
format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left 
join
37        + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 8 rows and 10 columns.

37        +                                                       quit;
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds
      
37        +                                                              proc sql; create table a3_5 as select  a3_2.branchid,  
      a3_2.customername,  a3_2.dealid,  a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,

38        + a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as 
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 34 rows and 10 columns.

38        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds
      
38        +
        proc append

39        + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run;  data a3_5;    set a3_5;    array change _numeric_;     
    do over change;             if change=. then change=0;         end;  run ;  proc sql outobs=100; create table a3_6 as select

NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 8 observations read from the data set WORK.A3_4.
NOTE: 8 observations added.
NOTE: The data set WORK.A3_5 has 42 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      


NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 70 observations read from the data set WORK.A3_3.
NOTE: 70 observations added.
NOTE: The data set WORK.A3_5 has 112 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

NOTE: There were 112 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 112 observations and 10 variables.
10                                                         Das SAS System

NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds
      

40        + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding, 
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
WARNING: Statement terminated early due to OUTOBS=100 option.
NOTE: Table WORK.A3_6 created, with 100 rows and 11 columns.

40        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.07 seconds
      

41        + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
Report\report_196_analysis3_LO.html";  options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=LO"; footnote "top 
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
      Report\report_196_analysis3_LO.html
42        + sorted by deviation descending";  proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid; 
column (obs branchid customername dealid dealstart dealend  riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
43        + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID'; 
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product 
Name'; define
44        + previousoutstanding / display  f=comma20. "Outstanding 202006"; define currentoutstanding / display  f=comma20. 
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
45        + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed  ; compute dev; 
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run;  options byline; title2;  
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER 
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60  SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN  ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
 ) );
 
DEFINE  obs / COMPUTED FORMAT= BEST9. WIDTH=9     SPACING=2   RIGHT "obs" ;
DEFINE  BranchID / GROUP FORMAT= $3. WIDTH=3     SPACING=2   NOPRINT LEFT "BranchID" ;
DEFINE  CustomerName / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Customer Name" ;
DEFINE  DealID / DISPLAY FORMAT= $30. WIDTH=30    SPACING=2   LEFT "Deal ID" ;
DEFINE  DealStart / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal Start" ;
DEFINE  DealEnd / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal End" ;
DEFINE  RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Risk Product Name" ;
DEFINE  PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202006" ;
DEFINE  CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202007" ;
DEFINE  dev / COMPUTED FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Relative Deviation" ;
DEFINE  deviation / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Absolute Deviation" ;

COMPUTE  obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;


COMPUTE  dev;
dev = currentoutstanding - previousoutstanding;
11                                                         Das SAS System

ENDCOMP;

RUN;

NOTE: There were 100 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
      real time           0.46 seconds
      cpu time            0.14 seconds
      

46        + proc sql;
46        +           create table a3 as select * from sasuser.production where branchID ="NY" and mandantid="196" and generation 
in ("202007","202006");
NOTE: Table WORK.A3 created, with 1088 rows and 59 columns.

46        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           51.29 seconds
      cpu time            10.12 seconds
      
46        +
        proc sql; create table a3_1 as select branchid,generation,customername, dealid, dealstart,

47        + dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3 where generation="202007" group by 
branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_1 created, with 355 rows and 8 columns.

47        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds
      
47        +
        proc sql; create table a3_2 as select

48        + branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from 
a3 where generation="202006" group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
NOTE: Table WORK.A3_2 created, with 356 rows and 8 columns.

48        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds
      
48        +
        proc sql;

49        + create table a3_3 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart, a3_1.dealend, 
a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  
a3_2.generation as
50        + PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x inner join a3_2 y on 
x.dealid=y.dealid;
NOTE: Table WORK.A3_3 created, with 247 rows and 10 columns.

50        +
12                                                         Das SAS System

 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.04 seconds
      cpu time            0.04 seconds
      
50        +
        proc sql; create table a3_4 as select  a3_1.branchid,  a3_1.customername,  a3_1.dealid,  a3_1.dealstart,

51        + a3_1.dealend, a3_1.riskproduct1name, a3_1.generation as CurrentGeneration, a3_1.Eoutstanding1 as CurrentOutstanding 
format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as PreviousOutstanding format comma20. from a3_1 x left 
join
52        + a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_4 created, with 108 rows and 10 columns.

52        +                                                       quit;
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.06 seconds
      
52        +                                                              proc sql; create table a3_5 as select  a3_2.branchid,  
      a3_2.customername,  a3_2.dealid,  a3_2.dealstart, a3_2.dealend, a3_2.riskproduct1name, a3_1.generation as CurrentGeneration,

53        + a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,  a3_2.generation as PreviousGeneration, a3_2.Eoutstanding1 as 
PreviousOutstanding format comma20. from a3_1 x right join a3_2 y on x.dealid=y.dealid where x.dealid<>y.dealid;
NOTE: Der Operator "<>" wird als "not equals" interpretiert.
NOTE: Table WORK.A3_5 created, with 109 rows and 10 columns.

53        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.06 seconds
      
53        +
        proc append

54        + base=a3_5 data=a3_4; run; proc append base=a3_5 data=a3_3; run;  data a3_5;    set a3_5;    array change _numeric_;     
    do over change;             if change=. then change=0;         end;  run ;  proc sql outobs=100; create table a3_6 as select

NOTE: Appending WORK.A3_4 zu WORK.A3_5.
NOTE: There were 108 observations read from the data set WORK.A3_4.
NOTE: 108 observations added.
NOTE: The data set WORK.A3_5 has 217 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      


NOTE: Appending WORK.A3_3 zu WORK.A3_5.
NOTE: There were 247 observations read from the data set WORK.A3_3.
NOTE: 247 observations added.
NOTE: The data set WORK.A3_5 has 464 observations and 10 variables.
NOTE: PROZEDUR APPEND used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
13                                                         Das SAS System


NOTE: There were 464 observations read from the data set WORK.A3_5.
NOTE: The data set WORK.A3_5 has 464 observations and 10 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds
      

55        + branchid, customername, dealid, dealstart, dealend, riskproduct1name, CurrentGeneration, CurrentOutstanding, 
PreviousGeneration, PreviousOutstanding, abs(currentoutstanding-previousoutstanding) as deviation from a3_5 order by deviation desc;
WARNING: Statement terminated early due to OUTOBS=100 option.
NOTE: Table WORK.A3_6 created, with 100 rows and 11 columns.

55        +
 quit;     
NOTE: PROZEDUR SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.09 seconds
      

56        + options nodate; ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
Report\report_196_analysis3_NY.html";  options nobyline; title "Drill Down Branches - Tenant ID=196 & Branch ID=NY"; footnote "top 
100 observations
NOTE: Writing HTML Body file: M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production 
      Report\report_196_analysis3_NY.html
57        + sorted by deviation descending";  proc report data=a3_6 nowd list headline headskip spacing=2 split='~'; by branchid; 
column (obs branchid customername dealid dealstart dealend  riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
58        + define branchid / group noprint; define customername / display 'Customer Name'; define dealid / display 'Deal ID'; 
define Dealstart / Display 'Deal Start'; define Dealend / Display 'Deal End'; define Riskproduct1name / Display 'Risk Product 
Name'; define
59        + previousoutstanding / display  f=comma20. "Outstanding 202006"; define currentoutstanding / display  f=comma20. 
"Outstanding 202007"; define deviation / display f=comma20. 'Absolute Deviation'; define dev / computed f=comma20.
60        + style=[backgroundcolor=colora.] 'Relative Deviation'; define obs / computed  ; compute dev; 
dev=currentoutstanding-previousoutstanding; endcomp; compute obs; _obs_ + 1; obs = _obs_; endcomp; run;  options byline; title2;  
ods html close;;
NOTE: Groups are not created because the usage of CustomerName is DISPLAY. To avoid this note, change all GROUP variables to ORDER 
variables.
PROC REPORT DATA=WORK.A3_6 LS=132 PS=60  SPLIT="~" HEADLINE HEADSKIP CENTER ;
COLUMN  ( ( obs BranchID CustomerName DealID DealStart DealEnd RiskProduct1Name PreviousOutstanding CurrentOutstanding dev deviation
 ) );
 
DEFINE  obs / COMPUTED FORMAT= BEST9. WIDTH=9     SPACING=2   RIGHT "obs" ;
DEFINE  BranchID / GROUP FORMAT= $3. WIDTH=3     SPACING=2   NOPRINT LEFT "BranchID" ;
DEFINE  CustomerName / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Customer Name" ;
DEFINE  DealID / DISPLAY FORMAT= $30. WIDTH=30    SPACING=2   LEFT "Deal ID" ;
DEFINE  DealStart / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal Start" ;
DEFINE  DealEnd / DISPLAY FORMAT= $20. WIDTH=20    SPACING=2   LEFT "Deal End" ;
DEFINE  RiskProduct1Name / DISPLAY FORMAT= $50. WIDTH=50    SPACING=2   LEFT "Risk Product Name" ;
DEFINE  PreviousOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202006" ;
DEFINE  CurrentOutstanding / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Outstanding 202007" ;
DEFINE  dev / COMPUTED FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Relative Deviation" ;
DEFINE  deviation / DISPLAY FORMAT= COMMA20. WIDTH=20    SPACING=2   RIGHT "Absolute Deviation" ;

COMPUTE  obs;
_obs_ +1;
obs = _obs_;
ENDCOMP;

14                                                         Das SAS System


COMPUTE  dev;
dev = currentoutstanding - previousoutstanding;
ENDCOMP;

RUN;

NOTE: There were 100 observations read from the data set WORK.A3_6.
NOTE: PROZEDUR REPORT used (Total process time):
      real time           0.49 seconds
      cpu time            0.12 seconds
      

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
61        + %html9(md=196,var=A,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
62        + %html9(md=196,var=C,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
63        + %html9(md=196,var=F,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
64        + %html9(md=196,var=I,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
65        + %html9(md=196,var=L,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
66        + %html9(md=196,var=N,curr=202007,prev=202006);
            _
            180
15                                                         Das SAS System


ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
67        + %html9(md=196,var=O,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
68        + %html9(md=196,var=S,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
69        + %html9(md=196,var=U,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
70        + %html9(md=196,var=X,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
71        + %html9(md=196,var=Y,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
72        + %html9(md=198,var=A,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
73        + %html9(md=198,var=C,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.
16                                                         Das SAS System


WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
74        + %html9(md=198,var=F,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
75        + %html9(md=198,var=I,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
76        + %html9(md=198,var=L,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
77        + %html9(md=198,var=N,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
78        + %html9(md=198,var=O,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
79        + %html9(md=198,var=S,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
NOTE: Line generated by the CALL EXECUTE routine.
80        + %html9(md=198,var=U,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent invocation of macro HTML9 not resolved.
17                                                         Das SAS System

NOTE: Line generated by the CALL EXECUTE routine.
81        + %html9(md=198,var=Y,curr=202007,prev=202006);
            _
            180

ERROR 180-322: Statement is not valid or it is used out of proper order.

31         
32         GOPTIONS NOACCESSIBLE;
33         %LET _CLIENTTASKLABEL=;
34         %LET _CLIENTPROCESSFLOWNAME=;
35         %LET _CLIENTPROJECTPATH=;
36         %LET _CLIENTPROJECTPATHHOST=;
37         %LET _CLIENTPROJECTNAME=;
38         %LET _SASPROGRAMFILE=;
39         %LET _SASPROGRAMFILEHOST=;
40         
41         ;*';*";*/;quit;run;
42         ODS _ALL_ CLOSE;
43         
44         
45         QUIT; RUN;
46         

[VCucu]

this is the complete code: i can run macro html3 as soon as i add another one (in this case macro html9) i get the error

/* <<< Analysis for Report No. 3 >>> */

%macro html3(md=,var=,curr=,prev=);

proc sql;
create table a3 as select * from sasuser.production
where branchID ="&var" and mandantid="&md" and generation in ("&curr","&prev");
quit;

proc sql;
create table a3_1 as select branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3
where generation="&curr"
group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
quit;

proc sql;
create table a3_2 as select branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a3
where generation="&prev"
group by branchid,generation,customername, dealid, dealstart, dealend,riskproduct1name;
quit;

proc sql;
create table a3_3 as select
a3_1.branchid,
a3_1.customername,
a3_1.dealid,
a3_1.dealstart,
a3_1.dealend,
a3_1.riskproduct1name,
a3_1.generation as CurrentGeneration,
a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as PreviousGeneration,
a3_2.Eoutstanding1 as PreviousOutstanding format comma20.
from a3_1 x inner join a3_2 y
on x.dealid=y.dealid;
quit;

proc sql;
create table a3_4 as select
a3_1.branchid,
a3_1.customername,
a3_1.dealid,
a3_1.dealstart,
a3_1.dealend,
a3_1.riskproduct1name,
a3_1.generation as CurrentGeneration,
a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as PreviousGeneration,
a3_2.Eoutstanding1 as PreviousOutstanding format comma20.
from a3_1 x left join a3_2 y
on x.dealid=y.dealid where x.dealid<>y.dealid;
quit;

proc sql;
create table a3_5 as select
a3_2.branchid,
a3_2.customername,
a3_2.dealid,
a3_2.dealstart,
a3_2.dealend,
a3_2.riskproduct1name,
a3_1.generation as CurrentGeneration,
a3_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a3_2.generation as PreviousGeneration,
a3_2.Eoutstanding1 as PreviousOutstanding format comma20.
from a3_1 x right join a3_2 y
on x.dealid=y.dealid where x.dealid<>y.dealid;
quit;

proc append base=a3_5 data=a3_4; run;
proc append base=a3_5 data=a3_3; run;

data a3_5;
set a3_5;
array change _numeric_;
do over change;
if change=. then change=0;
end;
run ;

proc sql outobs=100;
create table a3_6 as select
branchid,
customername,
dealid,
dealstart,
dealend,
riskproduct1name,
CurrentGeneration,
CurrentOutstanding,
PreviousGeneration,
PreviousOutstanding,
abs(currentoutstanding-previousoutstanding) as deviation
from a3_5
order by deviation desc;
quit;

options nodate;
ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production Report\report_196_analysis3_&var..html";

options nobyline;
title "Drill Down Branches - Tenant ID=&md. & Branch ID=&var.";
footnote "top 100 observations sorted by deviation descending";

proc report data=a3_6 nowd list headline headskip spacing=2 split='~';
by branchid;
column (obs branchid customername dealid dealstart dealend riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
define branchid / group noprint;
define customername / display 'Customer Name';
define dealid / display 'Deal ID';
define Dealstart / Display 'Deal Start';
define Dealend / Display 'Deal End';
define Riskproduct1name / Display 'Risk Product Name';
define previousoutstanding / display f=comma20. "Outstanding &prev.";
define currentoutstanding / display f=comma20. "Outstanding &curr.";
define deviation / display f=comma20. 'Absolute Deviation';
define dev / computed f=comma20. style=[backgroundcolor=colora.] 'Relative Deviation';
define obs / computed ;
compute dev;
dev=currentoutstanding-previousoutstanding;
endcomp;
compute obs;
_obs_ + 1;
obs = _obs_;
endcomp;
run;

options byline;
title2;

ods html close;

%mend html3;

/* <<< Analysis for Report No. 9 >>> */

%macro html9(md=,var=,curr=,prev=);

proc sql;
create table a9 as select * from sasuser.production
where exposureid ="&var" and mandantid="&md" and generation in ("&curr","&prev");
quit;

proc sql;
create table a9_1 as select exposureid,generation,customername, dealid, dealstart, dealend,Riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a9
where generation="&curr"
group by exposureid, generation,customername, dealid, dealstart, dealend,Riskproduct1name;
quit;

proc sql;
create table a9_2 as select exposureid,generation,customername, dealid, dealstart, dealend,Riskproduct1name, sum(Eoutstanding) as Eoutstanding1 from a9
where generation="&prev"
group by exposureid,generation,customername, dealid, dealstart, dealend,Riskproduct1name;
quit;

proc sql;
create table a9_3 as select
a9_1.exposureid,
a9_1.customername,
a9_1.dealid,
a9_1.dealstart,
a9_1.dealend,
a9_1.Riskproduct1name,
a9_1.generation as CurrentGeneration,
a9_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a9_2.generation as PreviousGeneration,
a9_2.Eoutstanding1 as PreviousOutstanding format comma20.
from a9_1 x inner join a9_2 y
on x.dealid=y.dealid;
quit;

proc sql;
create table a9_4 as select
a9_1.exposureid,
a9_1.customername,
a9_1.dealid,
a9_1.dealstart,
a9_1.dealend,
a9_1.Riskproduct1name,
a9_1.generation as CurrentGeneration,
a9_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a9_2.generation as PreviousGeneration,
a9_2.Eoutstanding1 as PreviousOutstanding format comma20.
from a9_1 x left join a9_2 y
on x.dealid=y.dealid where x.dealid<>y.dealid;
quit;

proc sql;
create table a9_5 as select
a9_2.exposureid,
a9_2.customername,
a9_2.dealid,
a9_2.dealstart,
a9_2.dealend,
a9_2.Riskproduct1name,
a9_1.generation as CurrentGeneration,
a9_1.Eoutstanding1 as CurrentOutstanding format comma20.,
a9_2.generation as PreviousGeneration,
a9_2.Eoutstanding1 as PreviousOutstanding format comma20.
from a9_1 x right join a9_2 y
on x.dealid=y.dealid where x.dealid<>y.dealid;
quit;

proc append base=a9_5 data=a9_4; run;
proc append base=a9_5 data=a9_3; run;

data a9_5;
set a9_5;
array change _numeric_;
do over change;
if change=. then change=0;
end;
run ;

proc sql outobs=100;
create table a9_6 as select
exposureid,
customername,
dealid,
dealstart,
dealend,
Riskproduct1name,
CurrentGeneration,
CurrentOutstanding,
PreviousGeneration,
PreviousOutstanding,
abs(currentoutstanding-previousoutstanding) as deviation
from a9_5
order by deviation desc;
quit;

options nodate;
ods html file="M:\OE0979\01 Systems\Cognos monthly testing\Cognos output\SAS Production Report\report_&md._analysis9_&var..html";
options nobyline;

proc format;
value colora -999999999999<-0='red' 0<-999999999999='green' other='white';
run;

title "Drill Down Exposure Class - Tenant ID=&md. Exposure Class ID=&var.";
footnote "top 100 observations sorted by absolute deviation descending";

proc report data=a9_6 nowd list headline headskip spacing=2 split='~';
column (obs exposureid customername dealid dealstart dealend Riskproduct1name PreviousOutstanding CurrentOutstanding dev Deviation);
define exposureid / group noprint;
define customername / display 'Customer Name';
define dealid / display 'Deal ID';
define Dealstart / Display 'Deal Start';
define Dealend / Display 'Deal End';
define riskproduct1name / Display 'Risk Product Name';
define previousoutstanding / display f=comma20. "Outstanding &prev.";
define currentoutstanding / display f=comma20. "Outstanding &curr.";
define deviation / display f=comma20. 'Absolute Deviation';
define dev / computed f=comma20. style=[backgroundcolor=colora.] 'Relative Deviation';
define obs / computed ;
compute dev;
dev=currentoutstanding-previousoutstanding;
endcomp;
compute obs;
_obs_ + 1;
obs = _obs_;
endcomp;
run;

options byline;
title2;

ods html close;
%mend html9;

/* Create Macro Run Table */

proc datasets library=sasuser;
delete prodrun;
run;

proc sql;
create table temp as select distinct 'html3' as proc, mandantid, branchid as var, '202007' as curr, '202006' as prev from sasuser.production
where branchid<>'?';
quit;

proc append base=sasuser.prodrun data=temp;
run;

proc sql;
create table temp as select distinct 'html9' as proc, mandantid, exposureid as var,'202007' as curr, '202006' as prev from sasuser.production;
quit;

proc append base=sasuser.prodrun data=temp;
run;

/* Execute Macro Run Table */

data _null_;
set sasuser.prodrun;
str=catt('%',proc,'(md=',mandantid,',var=',var,',curr=',curr,',prev=',prev,');');
call execute(str);
run;

[VCucu]

even more strange: if i put each macro in a separate program, so html3 in a program, and html9 in another program they both work with no problems... as soon as both macros are in the same program the first one works, the second one gets the respective error.

[andreas_lds]

Shame on me, i have not read all code you have posted and i am not sure to be 100% up-to-date, but some time ago each marcro had to be stored in a file named exactly like the macro for the autocall-mechanism to find the macros.

[Kurt_Bremser]

If you use the autocall facility, each file must contain exactly one macro, and the macro- and filename must be identical (save the .sas extension of the file).

[Tom]

Sound like you have not defined the macro before calling it.  Make sure that HMTL9 macro has been defined.

Also when using CALL EXECUTE to run a macro (especially a macro that is large or complex) make sure to use %NRSTR() so that just the CALL to the macro is pushed onto stack to run after the data step. Otherwise the macro runs  immediately when the CALL EXECUTE statement runs and all of the code statements that the macro generates are pushed onto the stack instead.  So instead of generating this command to pass to CALL EXECUTE:

%html3(md=196,var=A,curr=202007,prev=202006);

Wrap the macro name into %NRSTR() so you generate this instead:

%nrstr(%html3)(md=196,var=A,curr=202007,prev=202006);

You can do this by changing your data step like this:

data _null_;
  set sasuser.prodrun;
  length str $200;
  str=catt('%nrstr(%',proc,')(md=',mandantid,',var=',var,',curr=',curr,',prev=',prev,');');
  call execute(str);
run;

You will see impact in the log lines with the + at the start.

You will see one line like:

1        + %html3(md=196,var=A,curr=202007,prev=202006);

instead of multiple lines like:

1         + proc sql;
1         +           create table a3 as select * from sasuser.production where branchID ="DE" and mandantid="196" and generation 
in ("202007","202006");