DATA Step, Macro, Functions and more

Titles for multiple tables

Reply
Occasional Contributor
Posts: 13

Titles for multiple tables

Hi Experts,

i need help on how i can put tiles on five pairs of tables which are for imports and exports. i posted some questions earlier on but all the answers i received  were not applicable. some also wanted to know the code behind so i am posting the code for your review.

the program run perfectly but the title code does not work. this is excatly what appears on each of them 

 

 'US '||put(&amx,mx1fmt.)||' '||trim(left(put(&amx,mx3fmt.)))|| ' Company Defined '||trim(left(put(ctry_rgn,$rgn2fmt.)))||' - Million US $';

 

but i need this code to interprete the titles as  "US imports from Company Defined Nigeria - Millions US $" for one table and "US Exports from Company Defined Nigeria - Millions US $" and  dynamically apply titles to the rest of the tables.

 

 

 

%mk_formats;

run;

 

 

%prepdat1;

run;

 

%macro driver;

     data _null_;

          endhist=&endhist;

          beghist=intnx('year',endhist, -5);/*5years*/

          call symput ('beghist', trim(left(beghist)));

     run;

 

     %let page_count=36;

 

 

     *loop over countries;

     %do argn=1 %to 5 /*Nigeria, Haiti, Laos, Cuba, Egypt, World*/

 

          %let page_count=%eval(&page_count+1);

 

 

          %do amx=1 %to 2;

              %let page_count=%eval(&page_count+1);

 

              %mk_table1(&argn,&amx);

              run;

 

          %end;

     %end;

%mend driver;

 

*formats;

%macro mk_formats;

 

     proc format;

          value mx1fmt

              1='Imports'

              2='Exports'

              4='Imports'

              3='Exports'

          ;

 

     proc format;

          value mx2fmt

              1='Import'

              2='Export'

              4='Import'

              3='Export'

          ;

 

     proc format;

          value mx3fmt

              1='from'

              2='to'

              4='from'

              3='to'

          ;

 

     proc format;

          value rgn1fmt

              1='NA'

              2='HA'

              3='LA'

              4='CU'

              5='EG'

          ;

 

     proc format;

          value $rgn2fmt

              'NA'='Nigeria'

              'HA'='Haiti'

              'LA'='Laos'

              'CU'='Cuba'

              'EG'='Egypt'

              'WL'='World'

          ;

%mend mk_formats;

 

 

*********************************************************************************************;

*prepdat4 - data for table1;

*********************************************************************************************;

%macro prepdat1;

 

     data tmp_sum_wtadata(drop= ym);

          set data.sum_wtadata;

 

          if ctry_rgn ne 'WL';

 

          if year(cal) ge year(&endhist)-3;

 

          if ym=2;

 

     data tmpt(rename = (le=tle y=ty)) tmpa(rename = (le=ale y=ay))

              tmpv(rename = (le=vle y=vy)) tmpo(rename = (le=ole y=oy));

          set tmp_sum_wtadata;

 

          if tvo=1 then

              output tmpt;

 

          if tvo=2 then

              output tmpa;

 

          if tvo=3 then

              output tmpv;

 

          if tvo=4 then

              output tmpo;

 

     proc sort data=tmpt;

          by ctry_rgn descending mx cal;

 

     proc sort data=tmpa;

          by ctry_rgn descending mx cal;

 

     proc sort data=tmpv;

          by ctry_rgn descending mx cal;

 

     data tbl4data(drop=tvo);

          merge tmpa tmpv tmpt;

          by ctry_rgn descending mx cal;

 

%mend prepdat1;

 

 

%macro mk_table1(argn,amx);

 

     data tmp;

          set tbl1data;

          by ctry_rgn descending mx;/*All imports and exports*/

 

          if mx=&amx;

 

          if ctry_rgn=put(&argn,rgn1fmt.);

 

     title 'US '||put(&amx,mx1fmt.)||' '||trim(left(put(&amx,mx3fmt.)))||

          ' Company Defined '||trim(left(put(ctry_rgn,$rgn2fmt.)))||' - Million US $';

          output;

 

%mend mk_table1;

 

%driver;

 

 

 

Respected Advisor
Posts: 4,173

Re: Titles for multiple tables

[ Edited ]

I haven't read through your other posts so I'm may be repeating things others said already.

 

These two statements belong to the end of your code. You can't call a macro before it has been defined and compiled.

To test if you got the sequence right always spawn a new session so you don't use already compiled macros from a previous run in the same session.

%mk_formats;
run;

%prepdat1;
run;

 

I consider it as really "messy" to have the "run" statement outside of your macro definition. It's working but very hard to read. 

 

Is there an equal sign missing? And a lot of quoting? May be make this data step work first outside of a macro (if you need a macro at all).

     title ='US '||put(&amx,mx1fmt.)||' '||trim(left(put(&amx,mx3fmt.)))||

          ' Company Defined '||trim(left(put(ctry_rgn,$rgn2fmt.)))||' - Million US $';

          output;

 

And as a last comment: Re-consider your design and only use macros where really necessary. I don't see any reason for example for macro %macro mk_formats;

 

Occasional Contributor
Posts: 13

Re: Titles for multiple tables

There are three more tables that read from the macros
Occasional Contributor
Posts: 13

Re: Titles for multiple tables

Thanks for your suggestion but i used the equal sign  and the double quotes and it didnt work.  the warning message i get is " title statement is ambiuous due to invalid option or unquoted text".  iam trying to get a sample output for you may be it may help. 

thanks

Super User
Posts: 19,820

Re: Titles for multiple tables

 

 

I posted a sample title statement. 

Several things you missed in your TITLE statement. 

 

1. Use of double quotation marks instead of single

2. Use of %sysfunc to mask functions.

3. TITLE should be in proc print not your data step...where's your PROC PRINT/REPORT/TABULATE or are you trying to create a variable TITLE? 

 

If the code is only run once you don't need a macro, so why all the macros for formats and data prep?

 

Please post a clear example of sample data (tbl1data?) and expected output with sample titles  

 

 

 

 

 

Super User
Posts: 10,035

Re: Titles for multiple tables

You can use macro function to get it.

 

options nobyline;
data tmp;
set tbl1data;
by ctry_rgn desc mx;
format ctry_rgn $rgn2fmt. ;
.....................

title "US %sysfunc(putn(&amx,mx1fmt.)) %sysfunc(putn(&amx,mx3fmt.)) Company Defined #byvar1 - Million US $" ;

 

Occasional Contributor
Posts: 13

Re: Titles for multiple tables

Great  that was amaxing! your answer is about 90% complete. the first and the second parts worked perfectly but the last part which was linked to "mx=&amx" and "ctry_rgn=put(&argn, rgn1fmt)"  for the formats below did not work.

 

 

     proc format;

          value rgn1fmt

              1='NA'

              2='HA'

              3='LA'

              4='CU'

              5='EG'

          ;

 

     proc format;

          value $rgn2fmt

              'NA'='Nigeria'

              'HA'='Haiti'

              'LA'='Laos'

              'CU'='Cuba'

              'EG'='Egypt'

              'WL'='World'

 

 

below is also the original data tmp.

 

data tmp;

          set tbl1data;

          by ctry_rgn descending mx;/*All imports and exports*/

 

          if mx=&amx;

 

          if ctry_rgn=put(&argn,rgn1fmt.);

i believe the put is what is causing all these mess. 

 

Trusted Advisor
Posts: 1,570

Re: Titles for multiple tables

[ Edited ]

I have some comments to your code, with emphasize on organization.

At the end you'll find the code as I organized.

 

1)  Though it is legitimate to define a macro program within other macro program, like:

           %macro a;

                  ...

                    %macro b;

                         ...

                    %mend b;

                 ...

            %mend a;

     usually it is not recomended.

     Better take out the inner macro programs outside of the main (driver) macro program.

 

2) None of your formats use any macro variable, so no need to define them in a macro.

    if you save the format part as a seperate .sas file you can %include that file any time you need the formats.

    Alternativly you can save the formats in a catalog in your data library (run it once only):

           proc format lib=MY_LIBRARY;

                   ... all formats definition ...

                   ... no need to repeat the PROC FORMAT on seconf to the last one ...

            run;

     Thus will be creating my_library.formats catalog.  

     Then instead recreating the formats again and again you just need:

          options fmtsearh = (my_library);

 

3) In a macro, calling another macro, you might  get error if you use same macro variable name

     as exposed here:

      %macro A;

          %do ARG=1 to 5;

                 %macro_B(ARG = &ARG)

           %end;

       %mend A;

        (ASK YOURSELF: is ARG %global or %local ?!)

 

  In such case you can code, either:

      %macro A;

          %do ARGx=1 to 5;

                 %macro_B(ARG = &ARGx)

           %end;

       %mend A;

 

  OR

      %macro A;

          %do ARG=1 to 5;

                 %macro_B;   /* using &arg in macro_B, defined as %gloab arg;  not as argument of macro_B */

           %end;

       %mend A;

 

4) Better end each datastep and each procedure with RUN;  It is easier to read and maintain.

 

According to those comments I have reorganized your code.

You may try run it:

 

*************************** START *************************************************************;
proc format;     /* you can save formats by adding LIB=<any library>  */
value mx1fmt
1='Imports'
2='Exports'
4='Imports'
3='Exports'
;
value mx2fmt
1='Import'
2='Export'
4='Import'
3='Export'
;
value mx3fmt
1='from'
2='to'
4='from'
3='to'
;
value rgn1fmt
1='NA'
2='HA'
3='LA'
4='CU'
5='EG'
;
value $rgn2fmt
'NA'='Nigeria'
'HA'='Haiti'
'LA'='Laos'
'CU'='Cuba'
'EG'='Egypt'
'WL'='World'
;
RUN;
*********************************************************************************************;
*prepdat4 - data for table1;
*********************************************************************************************;
%macro prepdat1;
          data tmp_sum_wtadata(drop= ym);
            set data.sum_wtadata;

                  if ctry_rgn ne 'WL';

                 if year(cal) ge year(&endhist)-3;

                if ym=2;
         RUN;

data tmpt(rename = (le=tle y=ty)) tmpa(rename = (le=ale y=ay))
        tmpv(rename = (le=vle y=vy)) tmpo(rename = (le=ole y=oy));
  set tmp_sum_wtadata;

        if tvo=1 then     output tmpt;

        if tvo=2 then     output tmpa;

        if tvo=3 then     output tmpv;

        if tvo=4 then     output tmpo;
RUN;

proc sort data=tmpt;
   by ctry_rgn descending mx cal;
RUN;

proc sort data=tmpa;
   by ctry_rgn descending mx cal;
RUN;

proc sort data=tmpv;
   by ctry_rgn descending mx cal;
RUN;

data tbl4data(drop=tvo);
merge tmpa tmpv tmpt;
   by ctry_rgn descending mx cal;
RUN;

%mend prepdat1;

 

%macro mk_table1(argX,amx);
      data tmp;
       set tbl1data;
         by ctry_rgn descending mx;/*All imports and exports*/

             if mx=&amx;

            if ctry_rgn=put(&argX,rgn1fmt.);

          title 'US '||put(&amx,mx1fmt.)||' '||trim(left(put(&amx,mx3fmt.)))||
                 ' Company Defined '||trim(left(put(ctry_rgn,$rgn2fmt.)))||' - Million US $';
          output;

%mend mk_table1;


%macro driver;
     %GLOBAL ENDHIST;
     %PREPDAT1;

     data _null_;
          endhist=&endhist;
          beghist=intnx('year',endhist, -5); /*5years*/
          call symput ('beghist', trim(left(beghist)));
    run;

     %let page_count=36;

*loop over countries;
    %do argn=1 %to 5 /*Nigeria, Haiti, Laos, Cuba, Egypt, World*/

        %let page_count=%eval(&page_count+1);

        %do amN=1 %to 2;
              %let page_count=%eval(&page_count+1);

              %mk_table1(&argn,&amN);
       %end;
%end;
%mend driver;

%LET ENDHIST = ???? ;   /* I have not find assignment to that macro variable ! */
%driver;         /*  all formats have been already created and all macros were defined */

 

Occasional Contributor
Posts: 13

Re: Titles for multiple tables

thanks for your advice, all the information you gave has been put into good use. thanks once again.  

Occasional Contributor
Posts: 13

Re: Titles for multiple tables

thanks again, i have tried the %sysfunc for   if ctry_rgn=put(&argn,rgn1fmt.); but still not working. below are the codes i tried 

 

  if ctry_rgn=%sysfunc(putn(&argn,rgn1fmt.)); to pick %sysfunc(putc(ctry_rgn,$rgn2fmt)) for the countries. 

Trusted Advisor
Posts: 1,570

Re: Titles for multiple tables

What do you mean by "not working" ? 

Where in the code have you entered that statemnt ?

Are ERROR message(s) in the log ? - if so, post the full datastep log .

 

For now I can only guess -

    either you don't need the %sysfunc as it is part of a data step

    or you need  %IF ... if it is part of the macro programming.

 

Ask yourself - what is the difference between IF and %IF, and when to use each ?

Super User
Posts: 10,035

Re: Titles for multiple tables

You can't use %sysfunc(putn( in data step , only at open code place. use put() instead .
Ask a Question
Discussion stats
  • 11 replies
  • 391 views
  • 1 like
  • 5 in conversation