macro for excel file output (ods)

kjohnsonm — Thu, 22 Sep 2016 17:35:29 GMT

Hello all,

I am attempting to output in a data set text like this for auto caluclation in an excell file that SAS ODS produces for me:

=EXACT({table_name1}!A1,{table_name2}!A1)

=EXACT({table_name1}!A2,{table_name2}!A2)

=EXACT({table_name1}!A3,{table_name2}!A3)

=EXACT({table_name1}!A#...,{table_name2}!A#...)

My code:

%macro create(howmany);
   %do i=1 %to &howmany;
      data check_db;
	a&i.="=EXACT(census!A&i.,cendev!A&i.)"; 
	B&i.="=EXACT(census!B&i.,cendev!B&i.)"; 
	c&i.="=EXACT(census!C&i.,cendev!C&i.)";
 output;
      run;
   %end;
%mend create;

%create(1);

proc print data=check_db;
run;

My output:

Obs a1 B1 c1

1 =EXACT(census!A1,cendev!A1) =EXACT(census!B1,cendev!B1) =EXACT(census!C1,cendev!C1)

However when I put in 2, or 3, 4, ...

I still only get one line (1 obs) and the numbers all change to the number passed in. Can someone straighten me out here?

What I want is if I pass in 20 I get 20 obs all numbered from 1 to 20.

%create(2);

proc print data=check_db;
run;

                                                                                                                                                                                                                         14:52 Wednesday, September 21, 2016 837

Obs                a2                             B2                             c2

1     =EXACT(census!A2,cendev!A2)    =EXACT(census!B2,cendev!B2)    =EXACT(census!C2,cendev!C2)

PS I get no errors:

4097 %macro create(howmany);
4098     %do i=1 %to &howmany;
4099        data check_db;
4100      a&i.="=EXACT(census!A&i.,cendev!A&i.)";
4101 /*output;*/
4102      B&i.="=EXACT(census!B&i.,cendev!B&i.)";
4103 /*output;*/
4104      c&i.="=EXACT(census!C&i.,cendev!C&i.)"; output;
4105
4106        run;
4107     %end;
4108 %mend create;
4109
4110 %create(2);

NOTE: The data set WORK.CHECK_DB has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The data set WORK.CHECK_DB has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

4111
4112 proc print data=check_db;
4113 run;

NOTE: There were 1 observations read from the data set WORK.CHECK_DB.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

TIA. -KJ

Re: macro for excel file output (ods)

Astounding — Thu, 22 Sep 2016 17:51:10 GMT

Each iteration through the %DO is creating a complete DATA step. Even your log shows that two data steps completed.

If you want to create one DATA step, with additional assignment statements, you have to move two pieces:

- the DATA statement goes before the %DO loop

- the RUN statement goes after the %END statement

If you were to add this statement before running the macro, you could more easily follow what is happening:

options mprint;

Re: macro for excel file output (ods)

kjohnsonm — Thu, 22 Sep 2016 17:57:34 GMT

you rock! thank you. =EXACT(census!A4499,cendev! =EXACT(census!B4499,cendev! =EXACT(census!C4499,cendev!
=EXACT(census!A4500,cendev! =EXACT(census!B4500,cendev! =EXACT(census!C4500,cendev!

N = 4500

Re: macro for excel file output (ods)

kjohnsonm — Thu, 22 Sep 2016 17:58:33 GMT

%macro create(howmany);
data check_db;
	%do i=1 %to &howmany;
		A="=EXACT(census!A&i.,cendev!A&i.)";
		B="=EXACT(census!B&i.,cendev!B&i.)";
		C="=EXACT(census!C&i.,cendev!C&i.)";
	output;
	%end;
run;
%mend create;

%create(4500);
title 'Auto check matching counts';
proc print data=check_db noobs n;
run;
title;

topic Re: macro for excel file output (ods) in ODS and Base Reporting

macro for excel file output (ods)

Re: macro for excel file output (ods)

Re: macro for excel file output (ods)

Re: macro for excel file output (ods)