topic Re: Titles for multiple tables in SAS Programming

Titles for multiple tables

Kinston — Sat, 05 Nov 2016 23:56:24 GMT

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;

%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;

Re: Titles for multiple tables

Patrick — Sun, 06 Nov 2016 00:54:51 GMT

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;

Re: Titles for multiple tables

Reeza — Sun, 06 Nov 2016 00:35:47 GMT

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

Re: Titles for multiple tables

Kinston — Sun, 06 Nov 2016 02:30:50 GMT

There are three more tables that read from the macros

Re: Titles for multiple tables

Kinston — Sun, 06 Nov 2016 03:19:26 GMT

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

Re: Titles for multiple tables

Ksharp — Sun, 06 Nov 2016 05:08:12 GMT

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 $" ;

Re: Titles for multiple tables

Kinston — Sun, 06 Nov 2016 06:17:32 GMT

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.

Re: Titles for multiple tables

Shmuel — Sun, 06 Nov 2016 18:45:22 GMT

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;

%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 */

Re: Titles for multiple tables

Kinston — Mon, 07 Nov 2016 00:56:28 GMT

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

Re: Titles for multiple tables

Kinston — Mon, 07 Nov 2016 01:21:44 GMT

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.

Re: Titles for multiple tables

Shmuel — Mon, 07 Nov 2016 01:44:10 GMT

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 ?

Re: Titles for multiple tables

Ksharp — Mon, 07 Nov 2016 02:43:00 GMT

You can't use %sysfunc(putn( in data step , only at open code place. use put() instead .