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
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;
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;
%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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.